You are hereFeed aggregator / Categories / SQL Server Expert Blogs
SQL Server Expert Blogs
Efficiently Reuse Gaps in an Identity Column
SQLTeam.com - Mon, 02/08/2010 - 17:00
This article will demonstrate an efficient way to reuse gaps in an identity column. Please note that this is something you normally shouldn't be bothered about in a well-designed database or application. However, there are circumstances where you are forced to do this.
Categories: SQL Server Expert Blogs
Killing a SQL Server thread? Don’t!
Linchi Shea - Thu, 02/04/2010 - 13:09
Sometimes, when you kill a session (i.e. a spid) in a SQL Server instance, the spid just refuses to go away not because it’s doing a rollback. Perhaps, it’s stuck on a certain dependency on something external to SQL Server or it’s just simply stuck for...(read more)
Categories: SQL Server Expert Blogs
Finding Stuff in SQL Server Database DDL
Simple-Talk on SQL - Thu, 02/04/2010 - 01:00
You'd have thought that nothing would be easier than using SQL Server Management Studio (SSMS) for searching through the DDL for both the names and definitions of the structural metadata of your databases, for the occurrence of a particular string of letters. Not so easy, it turns out, though Phil Factor is able to come up with various methods for various purposes.
Categories: SQL Server Expert Blogs
Mission Critical: SQL Server 2008 Performance Tuning Task List
Simple-Talk on SQL - Thu, 02/04/2010 - 01:00
In which Buck Woody imagines how the US military would have tackled DBA checklists for 'mission-critical' databases. And having imagined it, lo, it came to be. The CTL for the DBA with short-back-and-sides.
Categories: SQL Server Expert Blogs
Simple Query tuning with STATISTICS IO and Execution plans
Simple-Talk on SQL - Thu, 02/04/2010 - 01:00
A great deal can be gleaned from the use of the STATISTICS IO and the execution plan, when you are checking that a query is performing properly. Josef Richberg, the current holder of the 'Exceptional DBA' award, explains how an apparently draconian IT policy turns out to be a useful ways of ensuring that Stored Procedures are carefully checked for performance before they are released
Categories: SQL Server Expert Blogs
Switching rows and columns in SQL
Simple-Talk on SQL - Thu, 02/04/2010 - 01:00
When they use SQL Server, one the commoner questions that Ms Access programmers ask is 'Where's the TRANSFORM/PIVOT command? So how do you swap colums and rows in an aggregate table? Do you really need to use a CLR routine for this?
Categories: SQL Server Expert Blogs
Writing Efficient SQL: Set-Based Speed Phreakery
Simple-Talk on SQL - Thu, 02/04/2010 - 01:00
Phil Factor's SQL Speed Phreak challenge is an event where coders battle to produce the fastest code to solve a common reporting problem on large data sets. It isn't that easy on the spectators, since the programmers don't score extra points for commenting their code. Mercifully, Kathi is on hand to explain some of the TSQL coding secrets that go to producing blistering performance.
Categories: SQL Server Expert Blogs
Implementing User-Defined Hierarchies in SQL Server Analysis Services
Simple-Talk on SQL - Tue, 02/02/2010 - 01:00
To be able to drill into multidimensional cube data at several levels, you must implement all of the hierarchies on the database dimensions. Then you'll create the attribute relationships necessary to optimize performance. Analysis Services hierarchies offer plenty of possibilities for displaying the data that your business requires. Rob Sheldon continues his seris on SQL Server Analysis Services 2008.
Categories: SQL Server Expert Blogs
SQL Search- The Search and the Sequel
Simple-Talk on SQL - Tue, 02/02/2010 - 01:00
It started out as an experiment to try to explore different ways of creating a software tool that people would want. It ended up as a tool that Red Gate is giving away to the SQL Server community in return for the contribution to the project of so many of Red Gate's friends within the community. But was it easy to do? Bob Cramblitt and Richard Collins went to find out by talking to Tanya Joseph, who managed the project that turned the concept into a product.
Categories: SQL Server Expert Blogs
What server is it trying to connect to?
Linchi Shea - Fri, 01/22/2010 - 18:54
It’s common to be called to find out why an app cannot connect to one of your database servers. So you start by checking the server, and it’s working just fine. Then, you check the client machine and its database connectivity to the server, and that is...(read more)
Categories: SQL Server Expert Blogs
Exceptional PowerShell DBA Pt 2 - The Morning Checklist
Simple-Talk on SQL - Thu, 01/21/2010 - 01:00
Laerte Junior takes us further into the world of an Exceptional PowerShell DBA, showing us how he uses PowerShell 2.0 to take all the headaches out of even more of his daily checklist. What could be better than having your morning checklist run itself?
Categories: SQL Server Expert Blogs
Laying out SQL Code
Simple-Talk on SQL - Thu, 01/21/2010 - 01:00
It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create an overall logic to the way it is done?
Categories: SQL Server Expert Blogs
Managing Data Growth in SQL Server
Simple-Talk on SQL - Thu, 01/21/2010 - 01:00
'Help, my database ate my disk drives!'. Many DBAs spend most of their time dealing with variations of the problem of database processes consuming too much disk space. This happens because of errors such as incorrect configurations for recovery models, data growth for large objects and queries that overtax TempDB resources. Rodney describes, with some feeling, the errors that can lead to this sort of crisis for the working DBA, and their solution.
Categories: SQL Server Expert Blogs
Brad's Sure DBA Checklist
Simple-Talk on SQL - Wed, 01/20/2010 - 01:00
Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist
Categories: SQL Server Expert Blogs
Celko's SQL Stumper: The Class Scheduling Problem
Simple-Talk on SQL - Tue, 01/19/2010 - 01:00
What can we use in SQL instead of E. F. Codd's T theta operators for best-fit? Joe Celko returns with another puzzle that isn't new, in fact it already features “Swedish”, “Croatian” and “Colombian” solutions in chapter 17 of Joe's 'SQL for Smarties' book. These were all written before CTEs or the new WINDOW functions. Is there now a better solution? Was there one even then? We leave it to the readers to provide the answer!
Categories: SQL Server Expert Blogs
Adding a PK online?
Tibor Karaszi - Wed, 01/13/2010 - 01:53
I just read in a forum about a user who want to replikate a table, but the table doesn't have a PK. The table is pretty large, and having the table not available while adding the PK is undesireable. The table has a clustered index already, and there are...(read more)
Categories: SQL Server Expert Blogs
Is there and overhead to RPC events?
Tibor Karaszi - Mon, 01/11/2010 - 03:31
I recently read a discussion whether RPC events add overhead compared to just submitting text. I got curious and did some testing, which I want to share. Background, SQL events Using most APIs, if you just submit a query to SQL Server, you will get what...(read more)
Categories: SQL Server Expert Blogs
Would you optimize SQL for less performance?
Linchi Shea - Fri, 01/08/2010 - 06:56
What do you mean? Okay, that does sound like an oxymoron, doesn't it? Let's say you are trying to optimize a stored procedure, and your proposed change results in the stored procedure running not faster, but perhaps a bit slower than it currently does....(read more)
Categories: SQL Server Expert Blogs
13 Things You Should Know About Statistics and the Query Optimizer
Simple-Talk on SQL - Thu, 01/07/2010 - 01:00
Fabiano launches into a sound technical explanation of the way that the query optimiser works in SQL Server with a mention of Brazilian Soccer stars and young ladies on Copacabana beach. You'll never quite think of statistics, execution plans, and the query optimiser the same way again after reading this, but we think you'll understand them better.
Categories: SQL Server Expert Blogs
Database Maintenance Plans in SSMS: An Overview
Simple-Talk on SQL - Wed, 01/06/2010 - 01:00
Maintenance plans are essential. Microsoft provide two tools, the Maintenance Plan Wizard and the Maintenance Plan designer, to assist the busy DBA to get started with one, but they don't include all the likely tasks, so Custom-created T-SQL or PowerShell scripts are usually required sooner or later to provide a more comprehensive plan
Categories: SQL Server Expert Blogs
