be wary of the results of sys.dm_db_index_physical_stats

my coworker (@developingjim) and his team was troubleshooting a client database production down issue this past weekend and discovered something that neither he nor i were aware of - the sys.dm_db_index_physical_stats function only scans index parent-level pages (leaf-level +1). the results of this versus running the function in DETAILED mode (which scans all pages and returns all statistics) can be drastically different. The image below shows two queries and the results for each:

The first query lead the team to believe that index fragmentation was not an issue; the second revealed that this was far from the truth and the crisis was resolved after the massive fragmentation was addressed

sql server best practices: disk configuration

• store the data and the log on different physical drives
• use the appropriate RAID level depending on the importance of performance and redundancy
• performance only - RAID 0
• redundancy only - RAID 1
• performance and redundancy - RAID 5 or RAID 10
• place files on subsystem connected to different controllers
• mirror the transaction log
• mirror the master and model system databases
• stripe the tempdb database
• store the user database files on a different physical drive from the master , model and tempdb system databases
• monitor the default filegroup or allow for automatic growth
• RAID, SAN and NAS appear as driver letters
• use direct-connected hard drivers or SAN instead of NAS or network file shares
• use NTFS

sql server best practices: database creation

when you create a new database in with the default options, sql server creates two files, the data file and the log file, in a single filegroup called PRIMARY. the files are created in the DATA sub-folder of the directory where you installed sql server unless you override the defaults (as you should). the state of the PRIMARY filegroup determines the state of the database

why this is bad: by only having one data file, user databases are stored in the same location as system databases. the majority of data changes occur in the the user databases. if the drive fails in the middle of a write operation, your data file becomes corrupt and all databases within become unusable. while having separate data files for your system and user databases avoids the previous situation, corruption to any file in the PRIMARY filegroup makes the database unusable.

what to do instead: minimally, all new databases should be created with two filegroups, two data files and a log file. the filegroups and the log file should all ideally be located on separate drives (which are hopefully RAID drives but more on best practices for RAID configurations for data log files in a future post). place one data file in the PRIMARY filegroup. this data file will contain the system databases and has a file extension of .mdf by convention. place the other data file in the second file group. this data file will contain user databases and has a file extension of .ndf by convention. change the default filegroup from PRIMARY to the second filegroup so new objects are created in the .ndf data file.

sql server best practices: tempdb

despite all of my lovely certifications and ten years of experience as the company dba, i am constantly faced with sql server performance issues (deadlocking more than anything else) that challenge or completely go against what i thought i knew about best practices. today, i learned that the default settings for the tembdb system database are far from adequate; the settings need to be changed based on the server environment. this is something that i've known and preached about with user databases (optimal hard disk RAID configurations, separation of physical and log files, etc. - i will cover this in another post), but i only really knew that the tempdb is recreated whenever the server is started and that the default size of the database files should be set large enough such that auto-growth never occurs. the following are some things i didn't know:

• the number of tempdb physical files should be equal to the number of processors or cores on the server. as the name implies, the tempdb stores temporary information required by sql server to complete some operation such as intermediate result sets and temporary tables created in queries. if there is only one tempdb data file, only one processor will be used to read and write information. to make use of all of the available processors, create one physical file per processor and set them all to be the same size.
• since the tempdb is recreated every time the server starts, redundancy is pointless. put the tempdb files on the fastest drives available
• auto-growth of the tempdb is bad for the same reasons that auto-growth is bad for user databases (file fragmentation, processing halts while the file grows, etc.) but, since the tempdb is constantly used for operations, the heinousness of auto-growth of the files grows exponentially with server load. don't be stingy with the default size - allocate enough disk space to ensure that there will always be enough available without depending on growth
• edit: divide the maximum amount of memory allocated to sql server (server properties -> memory) by the number of logical processors to get the recommended size of each tempdb physical file

edit: great reference

the effects of changing a machine's name on sql server

i decided to post about this because, i shit you not, every single member of our solution delivery department (aka our consultants) and a good number of our developers have approached me with issues after renaming their machine. one may be wondering why so many people are renaming their computer in the first place. simple - in my neck of the woods, we have several baseline virtual machine images built up for anyone to use. when someone begins using it, they typically give the 'computer' a new name (especially if they want to join it to the domain). the problem is that sql server doesn't pick up on this and programs that try to connect to the server by referencing it by the new name (or new name\instance name) will get errors along the lines of 'the server could not be found in the sysservers list. try adding the server using sp_addlinkedserver'. don't do that; it won't work. try running this command against the master database instead:

SELECT @@servername

it will probably return the name of the machine (\instance name) before you renamed it. the fix is this:

sp_dropserver '{oldname}'

where {oldname} is whatever was returned by your first query. after that, run this:

sp_addserver '{newname}(\instancename)', 'LOCAL'

obviously only include (\instancename) if it is a named instance of sql server. restart the sql server service, then re-run your original query and it should return the correct name (you will have to either open a new query editor or re-connect the query editor screen you originally used as restarting the service will cause a disconnect).

damned system named constraints

problem: you need to write a script to alter or drop a column as part of a hotfix for your clients. this column was created with a default constraint, but this default constraint was not explicitly named, thus a system-generated name was given (which looks something like 'DF__(partialtablename)__(partialcolumnname)__(random numbers and letters)' in SQL Server 2005). since the name of the constraint will be different on every client, you can't write a straight forward 'ALTER TABLE DROP CONSTRAINT ' statement.

solution: system tables aaaaaaaand ...

dynamic sql!!! weeeeeeeeeeeeeeeee

(i don't know why i got excited about something i genearlly preach against, but, as i've said before, dynamic sql is a necessary evil and can be very useful)

/*1. i think it is good practice to validate that something exists before dropping it (or does not exist before adding it). that way if a sql query that is part of a hotfix is accidentally run more than once, no errors should occur. this script simply checks to see if there is a constraint on a column named 'col_a' in table_a:*/

IF EXISTS (SELECT OBJECT_NAME(constid) FROM sysconstraints CN
INNER JOIN syscolumns CO
ON CN.id = OBJECT_ID('table_a') AND CO.id = CN.id AND
CO.name = 'col_a' AND CN.colid = CO.colorder)
BEGIN

/*2. the following sample then removes the constraint from the column:*/

DECLARE @Sql AS NVARCHAR(2000)

SELECT @Sql = 'ALTER TABLE table_a DROP CONSTRAINT ' + OBJECT_NAME(constid)
FROM sysconstraints CN
INNER JOIN syscolumns CO
ON CN.id = OBJECT_ID('table_a') AND CO.id = CN.id AND
CO.name = 'col_a' AND CN.coldid = CO.colorder

EXEC(@Sql)

END

/* to be extra safe, you may want to include the type of constraint you are dropping as part of the join. the type of constraint is contained in the pseudo-bit-mask value of the sysconstraints 'status' column. as an example, a default constraint will have a status value of '133141' */

selective filtering, part iii - dynamic sql filtering

i just thought of this as i posted my last entry and wanted to get it 'on paper' before it slips my mind.

if you are using dynamic sql to filter a query based on an optional/nullable parameter, you have to provide something to your WHERE clause in the case that you receive all nulls. here is an example that will not work using the clever ISNULL technique i discussed in my first post:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE col_b = ''' + ISNULL(@param1, 'col_b') + ''''

exec sp_executesql @sSQL
GO

what's the problem here? if @param1 is null, the WHERE clause is comparing the value of col_b to a constant, 'col_b', instead of setting it equal to itself. here is a correct way to accomplish the filter:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''

exec sp_executesql @sSQL
GO

in this case, you only filter if the @param1 contains a value. however, this once again presents a problem - what if there are multiple optional/nullable parameters provided? without knowing which ones contain values, you don't know where to put the WHERE. for example:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

this obviously breaks if both @param1 and @param2 are not null since you can't specify WHERE more than once. there are two options: write your query such that every combination of parameters containing and not containing values is accounted for (which is exponentially more difficult with each additional parameter), or you can be clever. let's rewrite the above sample:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE 1=1 '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

now that you've specified a condition that's always true (WHERE 1=1), you can dynamically add additional filters as needed. pretty damn slick if i do say so myself

one last thing - i always declare @sSQL as nvarchar(4000) in my examples because sp_executesql only works with variables with data types of nchar, ntext and nvarchar and the maximum size of an nvarchar variable used with sp_executesql is 4000

selective filtering, part ii - dynamic sql sorting 1

my previous post on selective filtering dealt with filtering a select statement if a non-null parameter value is passed to your stored procedure without using dynamic sql. however, there will be times when dynamic sql is required. since the 'sp_executesql' stored procedure was included in SQL 2005 and was not removed in SQL 2008 (to my best knowledge), i dare anyone to dispute that (but if you do so successfully i will be simultaneously amazed, humbled and eternally grateful).

for the uninitiated, dynamic sql is the process of building a query on-the-fly based on parameters passed to the stored procedure. an example of when this is required is if your query accepts the name of a column as a parameter (@param1) and you want to sort your query based on this column/parameter. you can't order a query by a constant, null, or a parameter, even if the parameter evaluates to a valid column name. instead, you write something like this:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' ORDER BY ' + @param1

exec sp_executesql @sSQL
GO

assuming @param1 is a valid column name in table_a, the statement will run and return the values of col_a ordered by the @param1 column (in ascending order by default). notice that i checked to make sure @param1 contained some value since you can't order by null and, if i hadn't checked and @param1 was null, @sSQL would be null (since anyting + null = null). sp_executesql won't throw an error if @sSQL is null; it will just say "Command(s) completed successfully." and return nothing.

that does it for the basics of dynamic sql sorting. the reason i named this post 'dynamic sql sorting 1' is because i can think of at least one more complicated case (e.g. using a case statement to select a column to sort by when another column's value is equal to an input parameter) that i don't want to go into right now since this post is already pretty long. good luck and by all means let me know if you have any comments, corrections or questions. später

deadlocks are bad

what is a deadlock? the way i typically explain it is like so (this is simplified but i think it gets the idea across):

• process A has a lock on table A and is waiting to update it with information from table B
• process B has a lock on table B and is waiting to update it with information from table A

since neither process will release its lock until the other processes releases its lock, we have ourselves a deadlock. most of the time SQL server "resolves" this issue itself because it has a thread dedicated to its lock manager and someone much smarter than myself came up with an algorithm that allows the lock manager to detect this and kill one of the processes. i have no clue how it chooses which process is the victim of its kill statement, but, from my experience, 99% of the time it is does make this decision and it does kill one of the processes. i've not yet had this happen in SQL 2005, but i can count on one hand the number of times when a deadlock occurred in SQL 2000 and SQL left the decision up to me. in all of these cases, the decision for me was simple - i opened up the SQL activity monitor, scrolled to the right, and saw that a whole bunch of locked processes listed the same process in the 'Blocked By' column. by no means should you be cavalier and just kill this single process before you know what it is, but it's a good place to start. i figured out what the process was and had my client log on to the server hosting the application and simply close it.

back to the other 99% of the time SQL Server resolved the deadlock automagically. this is still very bad. some application was trying to perform some operation on a table or its data and SQL Server flat out squashed it before it could finish. there are certainly ways design a database to minimize the chance of this, but no matter how badass you think your 5NF database is (because there is no way you are badass enough to reach 6NF), you are going to experience a deadlock at some point. i recommend you read the following article to better understand what is going on, and how to track down resolve the problem:

http://support.microsoft.com/kb/832524

selective filtering, part i

what if you only want to filter a select statement if a non-null value is passed to your query as a parameter? you have a couple of options. one is to use dynamic sql to build your WHERE clause and only include the filter if the parameter value is not null. however, as i will attempt to establish as law at one point or another, dynamic sql is bad and should be avoided at all costs. from my experience, the easiest and best way to accomplish this without resorting to dynamic sql is to make use of the ISNULL function like so:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)

a.column_a = a.column_a is always true if a.column_a does not contain nulls, thus if a null is passed to @param1, the statement will not be filtered. if the column can contain nulls, we have to be a be more creative depending on what you want to accomplish. what about using the SET ANSI_NULLS OFF option? this allows for the logical comparison of nulls (i.e. null = null evaluatues to true). doing so makes the example above return all rows when @param1 is null. if you do not want rows returned where a.column_a is null, simply add an additional line:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)
AND a.column_a IS NOT NULL

problem solved. one last thing i should note is my example is likely a much simpler example than what you have since i am only concerned with one column; you must take caution to make sure your other comparisons will not return undesired results when using SET ANSI_NULLS OFF.