Cleveland DBA

SQL Server administration in Cleveland, OH

SQL Server A to Z – Collation

One of the first decisions you need to make when installing SQL Server is to select a collation for the instance.  Most of the time we simply choose the default and breeze on by.  But do you really know all the effects that choice has on your database and the applications that run against it?

What is the default?

By default, SQL Server chooses the collation that corresponds to the Windows system locale on your database server.  You can see your current locale in the Regional and Language Options section of Control Panel. The default for U.S. servers is SQL_Latin1_General.

What does it affect?

The collation you choose will have 3 effects on your system.

  • the code page – This is the mapping between the hex values that get stored in the database and the character they represent.  It’s what makes 0x43 the letter C for the Latin alphabet.
  • case sensitivity – Your collation determines whether C = c.  This obviously has an impact on your applications and the data they’ll bring back during searches and joins.  But an important thing to keep in mind is that it also impacts whether SELECT * FROM MyTab is the same as SELECT * FROM mytab.  If you choose a case sensitive collation setting on installation, MyTab != mytab.
  • sort order – The collation you select will also affect the sort order of query results.  In a case sensitive dictionary sort (the kind used by Latin1_General_CS_AS), “cat” will come before “Cat”.  However, in a binary sort order (Latin1_General_BIN), “Cat” comes before “cat”.  In fact, “Toad” will come before “cat”, because the order is based on the bit value of the character.

Where is it set?

The collation of the server is selected at install time.  This becomes the collation for all the system databases and any user databases that are created without specifying a collation.  However, an individual user database can have a different collation from the SQL Server instance.  And a column inside a table can have a different collation than the database or the instance. The database and column collation can be changed relatively easily.  The only “easy” way to change the server collation is to rebuild the instance, so it’s worth taking a moment to think about your collation before installation.

What about Unicode?

SQL Server collations do not affect Unicode datatypes, nchar, nvarchar, etc.  SQL Server collations are the ones that start with “SQL_”.  These are provided to offer backward compatibility.

Cannot resolve collation conflict … for equal to operation

This, in my mind, is the biggest headache associated with collations.  If you try to join 2 columns of incompatible collations, a case sensitive with a case insensitive for example, you’ll get a variation on the above error.  SQL Server doesn’t know which collation’s rules to follow.  To get around this, you’ll need to include the COLLATE clause in your query, like this:

USE AdventureWorks;
GO

SELECT * FROM MyTabCI ci INNER JOIN MyTabCS cs ON ci.ColA COLLATE Latin1_General_CS_AS = cs.ColA

This will tell SQL Server which collation to use for the join.

That’s about it.  The important thing to remember with collations is that a little thought up front can save you a lot of hassle down the road.

July 14, 2011 Posted by | Features, General | , | Leave a comment

SQL Server A to Z – Backups

One of the most important, if not the most important tasks of the DBA is backing up your database.  The database backup is the most basic disaster recovery plan you can have in place.  It’s certainly the easiest to implement.  Today I’m going to go over the basics of backups, including the different types of backups and how they work.

Full Backup

A full backup of a database includes all data pages in the database and just enough of the transaction log to be able to recover up to the end time of the backup.  Curious as to all the internal steps involved in a full backup?  Turn on trace flags 3004 and 3605 using DBCC TRACEON (3004, 3605, -1) and perform a backup a backup.  Then take a look at your errorlog.  Just don’t forget to turn off the trace flags when you’re done, since they can cause some performance degradation in your backups.

Differential Backup

A differential backup is an incremental backup of all changed data since the last full backup.  The last full backup.  Not the last differential backup.  This is an important distinction to keep in mind because a lot of new DBAs (especially former Oracle DBAs) think that, in the event of a restore, they need to restore the full backup and then all of the differential backups taken after.  And that’s simply not the case.  You only need to restore the latest differential.

So how do differentials work?  SQL Server uses something called a Differential Change Map (DCM) page to track changes to extents.  The 6th page in every data file is a DCM page, and it tracks about 4GB worth of data.  So, for every 4GB of data in your database, there’s a corresponding DCM keeping watch.  Let’s take a quick look.

-- Start with a full backup
BACKUP DATABASE [AdventureWorks] TO  DISK = N'D:\SQL2008\Backup\AdventureWorks.bak' WITH NOFORMAT, INIT,  NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Look at the DCM
DBCC TRACEON (3604)
DBCC PAGE('adventureworks',1,6,3)
GO

Scroll down through the results until you get to the DIFF MAP section. Your results should look something like this:

DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2

(1:0) – (1:24) = CHANGED
(1:32) – (1:21752) = NOT CHANGED

Now let’s update some data and take another look.

-- update some data
update Production.TransactionHistory
set Quantity = 100
GO

-- check out the differential map now
DBCC PAGE('adventureworks',1,6,3)
GO

Again, scroll through the results to the DIFF MAP section. Now it probably looks more like this:

DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2

(1:0) – (1:24) = CHANGED
(1:32) – (1:152) = NOT CHANGED
(1:160) – = CHANGED
(1:168) – (1:224) = NOT CHANGED
(1:232) – = CHANGED
(1:240) – (1:20096) = NOT CHANGED
(1:20104) – (1:20160) = CHANGED
(1:20168) – (1:20224) = NOT CHANGED
(1:20232) – (1:20288) = CHANGED

So by reading 1 page for every 4GB of data, SQL Server knows exactly what extents have been changed and therefore knows what extents need to be backed up. And that’s what makes differential backups so fast. If we were to perform a differential backup now and check the DCM page, we’d see no difference. However, if we performed a full backup, the DCM page would revert back to its original state.

Note that you can take a special type of full backup, called a Copy-Only backup that will not reset the DCM pages.  Use this if you need to take a one-off backup, say to refresh a Dev environment, without interrupting your normal backup plan.

File Backup

Individual database files can be backed up and restored using full or differential file backups.  This is helpful if only one data file is corrupted or lost and needs to be restored.  File backups can also make backing up very large databases more manageable.  But you really need to think through your backup/restore strategy if you’re using file backups.  Miss one file and you could render your database unrecoverable.  In my humble opinion, they’re generally not worth the administrative nightmare.

Partial backup

SQL Server 2005 introduced partial backups.  Like file backups, partial backups are helpful in very large databases, however they don’t entail quite so much administration.  A full or differential partial backup will back up all read/write filegroups in the database and, optionally, any read-only filegroups you specify.

Transaction Log Backup

If your database is in Full or Bulk-Logged recovery mode, you need to take transaction log backups.  A transaction log backup backs up the active portion of the transaction log, including all transactions since the last log backup.  If you’re using Bulk-Logged recovery, the log backup also includes the actual data pages for any minimally logged transactions.  Once a log backup is performed, that portion of the transaction log is freed up for reuse.  This prevents those pesky transaction log full (9002) errors.

July 11, 2011 Posted by | Features, General, Maintenance | , , , | Leave a comment

Scripting out a database – revisited

Doh!  A while back I posted a Powershell script that would script out a SQL Server database.  Well, a reader pointed out to me yesterday that it wasn’t quite working for him.  And wouldn’t you know, it wasn’t working for me either.  The problem was that when I created the script I had a specific database in mind.  And this database resided on a SQL 2005/Powershell 1.0 server.  My script worked like a champ there.  On a SQL 2008/Powershell 2.0 server, well, not so much.  So I spent some time yesterday making some fixes and I think I’ve got it this time (for either system).

Many thanks to William for the heads up.

The new code:

# SQLExportDBSchema.ps1
# Usage -
# From PS:  .\SQLExportDBSchema.ps1 "[Drive letter]" "[Server]" "[Database]"
# From CMD: powershell -command "& 'D:\powershell\SQLExportDBSchema.ps1' "[Drive letter]" "[Server]" "[Database]" "

param(
	[string]$drive=$null,
	[string]$srv=$null,
	[string]$database=$null
	)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$filepath = $drive+":\"+$srv+"_backup\"+$database+"\"

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srv
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")

$db=$s.Databases[$database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $False
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.Permissions = $True

#CREATE DATABASE
$scrp.Options.AppendToFile = $False
$outfile =$filepath+$database+"_create.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db)

#SECURITY
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_security.sql"
$scrp.Options.FileName = $outfile
$scrp.Options.IncludeDatabaseRoleMemberships = $true
Foreach ($ro in $db.Roles)
{
   $scrp.Script($ro)
   $scrp.Options.AppendToFile = $True
}
$scrp.Options.AppendToFile = $True
Foreach ($appro in $db.ApplicationRoles)
{
   $scrp.Script($appro)
}
Foreach ($us in $db.Users)
{
   $scrp.Script($us)
}
Foreach ($sch in $db.Schemas)
{
   $scrp.Script($sch)
}

$scrp.Options.WithDependencies = $True

#PARTITIONING
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_partitioning.sql"
$scrp.Options.FileName = $outfile
Foreach ($part in $db.PartitionFunctions)
{
   $scrp.Script($part)
   $scrp.Options.AppendToFile = $True
}

$scrp.Options.AppendToFile = $True
Foreach ($psch in $db.PartitionSchemes)
{
   $scrp.Script($psch)
}

#TABLES
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_tables.sql"
$scrp.Options.FileName = $outfile
Foreach ($tab in $db.Tables)
{
   $scrp.Script($tab)
   $scrp.Options.AppendToFile = $True
}

#DDLTRIGGERS
$scrp.Options.WithDependencies = $False
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_triggers.sql"
$scrp.Options.FileName = $outfile
Foreach ($trig in $db.Triggers)
{
   $scrp.Script($trig)
   $scrp.Options.AppendToFile = $True
}

$scrp.Options.WithDependencies = $True

#VIEWS
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_views.sql"
$scrp.Options.FileName = $outfile
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scrp.Script($views)

#FUNCTIONS
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_functions.sql"
$scrp.Options.FileName = $outfile
$functions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$scrp.Script($functions)

#STORED PROCS
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_storedProcs.sql"
$scrp.Options.FileName = $outfile
$storedprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$scrp.Script($storedprocs)

#SYNONYMS
$scrp.Options.AppendToFile = $False
$outfile = $filepath+$database+"_synonyms.sql"
$scrp.Options.FileName = $outfile
Foreach ($syn in $db.Synonyms)
{
   $scrp.Script($syn)
   $scrp.Options.AppendToFile = $True
}

July 6, 2011 Posted by | Features, General, Maintenance | , | 2 Comments

Patience

Patience. Patience is a virtue that I’ve been struggling with this week. I’ve been working with a person who adamantly believes that the SQL Server backup process locks tables in the database and will therefore cause his application to fail. I explained that this simply isn’t the case. I quoted numerous reputable sources. I offered examples from his own application. I ran a backup concurrent to his application, successfully. I walked through every line of the code, on the off chance it was doing something wacky. It isn’t. I ran tests specifically designed to cause the blocking he insists will happen. No blocking. No failures.

And yet, none of this mattered. He believes what he believes.

Why should I care?

Paul Randal wrote about ignorance yesterday. As he pointed out, we’re all ignorant. There are vast quantities of things I am ignorant about. There’s nothing wrong with that. However, ignorance is one thing, willful ignorance in the face of all evidence to the contrary is something else. This guy (the first guy, not Paul) is not a SQL Server expert, and I don’t expect him to be. But what I do expect from a professional is the capacity to acknowledge that there are others who might know more than you about some things. And you might do well to hear what they have to say.

I left the conversation hanging. As I said, my patience was wearing thin, and I certainly didn’t want the exchange to devolve into something I’d regret later. But, I would love to hear from some of you out there on this. Even if it’s to tell me I’m being a judgemental ass.

Really, how do you deal with situations like this?

July 1, 2011 Posted by | General, Professional Development | Leave a comment

What I’m working on

This has been a somewhat scattered week.  As I alluded to earlier, my mind has been pulled in all different directions after a week of training and listening to presentations.  I really have to work on writing my ideas down somewhere, as I have them, and getting them out of my head until I’m ready to deal with them.  At the moment, I’m into a few projects.

Automating development database creation

As a takeaway to the “4 hour DBA” session this weekend, I’ve decided to create a way to allow developers to create their own databases/logins in our Dev environment.  Rather than grant them all db_creator and security_admin and, well, create the Wild Wild West, I’m working with one of our developers to create a web form front end that will pass required parameters to a stored procedure.  The procedure will check for the existence of the database, and the existence of the requested login.  If the login exists, the developer will need to provide the correct password.  An application name will also be required, and will be logged along with the database name, db_owner, creation date, and the developer’s login.  One of the biggest problems we have is databases in Dev that are “forgotten”.  A developer requests it and somewhere down the line everyone forgets about it and what it was for to begin with and I’m left asking everyone “do you know anything about database X?”  Not fun.  And why me, you might ask?  Because I’m the one who developed and maintains our SQL Server inventory database.  Which leads me to…

Upgrading our SQL Server inventory to 2008

A long time ago I wrote a few SSIS packages that go to each SQL Server instance and collect different information and store it in a central repository.  This is in SSIS 2005 currently.  It also resides in our Dev environment.  Leaving it in Dev was fine for a long time, the DBAs were the only ones who used the information.  But recently I’ve opened it up to other users who were looking for that information, and I’ve published some MS Reporting Services reports for our Windows Admin group to reference when they’re scheduling outages, etc.  So, it needs to go to Production.  And if I’m doing that, I might as well migrate it to SSIS 2008.  And if I’m doing that, I might as well take another look and see if there’s a better way to do this.

Preparing a presentation

Another thing on my plate is preparing my very first presentation.  At work we have monthly developer forum presentations on a variety of subjects, and I volunteered to present on SQL Server features that developers could be using but probably aren’t.  (Note to self: Need to come up with a snappier title.)  I’m planning on covering Service Broker, database snapshots, and data compression.  All topics that I’ve covered here and I feel comfortable enough with to present to my coworkers.  It’s not going to happen for a couple of months, but I really need to start organizing my thoughts and making notes.

There are other things I’m doing, of course, routine tasks that are simply part of the job. But these are the bigger ones I’m focusing on now.  The ones that I’ll be working on when my instant messenger status changes to “Do Not Disturb.”  🙂

June 16, 2011 Posted by | General, Professional Development | | Leave a comment

Stuff I learned today – XML and CLR

Well, to say I “learned” about XML and CLR is a bit disingenuous.  More accurately, the instructor went over XML and CLR.  But to be honest, I know so little about either subject that it was a bit like listening to Charlie Brown’s teacher.  But anyway, we covered it and it gave me some ideas as to how they can be used, so I’ll have more to offer the next time a developer comes to me asking “How can I…”  And that was kind of the point of picking this class in the first place.

In other news I’m heading down to Columbus this weekend for SQL Saturday #75.  I’m looking forward to some good sessions to cap off this Week ‘o SQL Learnin’.

Have a great weekend folks!

June 10, 2011 Posted by | Features, General, Professional Development | , , | Leave a comment

Scripting out a database

Switching gears a little bit, much of my attention this week has revolved around backups. We have several large databases that have gotten to the point where full backups every night have become unwieldy. So I’ve been redesigning the maintenance plans for them around a full backup once a week and differentials nightly. So far it’s working out very well, saving us a lot of time, disk, and tape. Woot!

While I was thinking about backups in general, I also started thinking about a question that’s come up before: how to script out all the database objects in an automated fashion. Whether it’s so you can create a second ’empty shell” copy of your database, or so you can restore a single stored procedure that you accidentally dropped, or just because it’s a development environment and you don’t care about the data so much as the object definitions. (Or because it’s just cool.)  Whatever the reason, I personally think this is something that could come in handy, and I hadn’t come across any way in SQL Server to automate the scripting a database.

So i did some searching and found a few third party tools (nah), some options that revolved around VB or C# or something (yeah, have I mentioned I’m not a programmer?). Then I came across a few partial solutions using powershell. Now, again me != programmer, but I can handle a little bit of powershell. They were pretty straightforward, but they weren’t quite… enough. So I took a little of each, threw in a few touches of my own, and voila, a solution that I’m pretty darn pleased with. Can it be improved upon? I fairly confident it can be. And probably will be. But in the meantime it gets the job done. And it can be scheduled either through a database job or via the Windows scheduler, which was the whole point.  And so, without further ado, I present… the script… (ooooooo!)

# SQLExportDBSchema.ps1
# Usage -
# From PS:  .\SQLExportDBSchema.ps1 "[output location]" "[Server]" "[Database]"
# From CMD: powershell -command "& 'D:\powershell\SQLExportDBSchema.ps1' "[output location]" "[Server]" "[Database]" "

param(
	[string]$filepath=$null,
	[string]$srv=$null,
	[string]$database=$null
	)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$filepath = $filepath+"\"

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srv

$db=$s.Databases[$database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $False
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.Permissions = $True

#CREATE DATABASE
$outfile =$filepath+$database+"_create.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db)

#SECURITY
$outfile = $filepath+$database+"_security.sql"
$scrp.Options.FileName = $outfile
$scrp.Options.IncludeDatabaseRoleMemberships = $true
$scrp.Script($db.Roles)
$scrp.Options.AppendToFile = $True
$scrp.Script($db.ApplicationRoles)
$scrp.Script($db.Users)
$scrp.Script($db.Schemas)

$scrp.Options.AppendToFile = $False
$scrp.Options.WithDependencies = $True

#PARTITIONING
$outfile = $filepath+$database+"_partitioning.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.PartitionFunctions)
$scrp.Options.AppendToFile = $True
$scrp.Script($db.PartitionSchemes)

$scrp.Options.AppendToFile = $False

#TABLES
$outfile = $filepath+$database+"_tables.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Tables)

#DDLTRIGGERS
$outfile = $filepath+$database+"_triggers.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Triggers)

#VIEWS
$outfile = $filepath+$database+"_views.sql"
$scrp.Options.FileName = $outfile
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scrp.Script($views)

#FUNCTIONS
$outfile = $filepath+$database+"_functions.sql"
$scrp.Options.FileName = $outfile
$functions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$scrp.Script($functions)

#STORED PROCS
$outfile = $filepath+$database+"_storedProcs.sql"
$scrp.Options.FileName = $outfile
$storedprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$scrp.Script($storedprocs)

#SYNONYMS
$outfile = $filepath+$database+"_synonyms.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Synonyms)

May 5, 2011 Posted by | Features, General, Maintenance | , , | 3 Comments

How is SQL Server using all that memory, anyway?

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, that I find very handy for getting an overall picture of how memory is being used by a SQL instance. Personally I run it as a complete script, but I’ll break it down here and provide a little description of each section. I should say, too, that I didn’t write any of this from scratch. For most of it I started with code found here, here, and here, and modified it to suit.

So, without further ado, let’s get started. The first section provides an big-picture look at how SQL is currently allocating memory between the database page cache, procedure cache, and miscellaneous memory usage. It also provides the total memory usage, and I added in the maximum available workspace memory.

--HOW MUCH MEMORY IS MY SQL SERVER USING?

declare @plan_cache_size float, @obj_data_size float , @avail_workspace_size float

-- plan cache size

select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages' and instance_name = '_Total'

-- DATABASE PAGE CACHE (PAGES, INCLUDING INDEXES)

select @obj_data_size = (cntr_value*8)/1024.0  FROM sys.dm_os_performance_counters
	WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'

-- Maximum workspace available for sorts, hashes, etc

select @avail_workspace_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)'

-- CACHE TOTALS
select   @obj_data_size [Database_Page_Cache_MB]
 , @plan_cache_size [Procedure_Cache_MB]
 , [Misc_Memory_Usage_MB]
 , [Misc_Memory_Usage_MB] + @obj_data_size + @plan_cache_size [Total_Memory_Usage_MB]
 , @avail_workspace_size [Maximum_Workspace_MB]
from
(
 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]
 from sys.dm_os_performance_counters
 where object_name like '%memory%'
 and (
   counter_name like '%Connection Memory (KB)%'
   or
   counter_name like '%Granted Workspace Memory (KB)%'
   or
   counter_name like '%Lock Memory (KB)%'
   or
   counter_name like '%Optimizer Memory (KB)%'
   or
   counter_name like '%SQL Cache Memory (KB)%'
 )
) x
GO

The output:

The next section uses sys.dm_os_memory_cache_counters to examine how the procedure cache is being broken down.

-- How the Procedure cache is being used

SELECT  TOP 6
	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
	[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

The output:

The third part of the script goes deeper into the procedure cache and displays the top 25 cached plans by size. Because it displays some application code, I’m not going to include the results here.

-- Top cached plans by size

select top 25
cacheobjType
, ObjType
, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]
, db_name(dbid) [database_name]
, object_name(objid, dbid) [object_name]
, [sql]
from master..syscacheobjects (nolock)
order by pagesUsed desc
GO

And, finally, the last section shows us how much of the buffer pool is being utilized by each database, including the dirty and clean page counts, sorted by total memory.

-- Buffer Pool Memory Per DB

SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
count(*)AS TotalPageCount,
cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO

And the output:

And there you have it. I hope you find this useful!

May 2, 2011 Posted by | General, SQL Tuning, Troubleshooting | , , , | 1 Comment

Available Workspace Memory

My research into the Resource Governor question and its answer brought up another question for me:

SELECT * FROM sys.dm_exec_query_resource_semaphores

Where does that total_memory_kb number for the default pool actually come from? After all, I have a max server memory of 26000MB for the instance, so why 18175MB?  The BOL description for this field wasn’t very helpful.  Googling for total_memory_kb didn’t really yield me much.  Most of what I found was unrelated to this particular DMV.

So I started looking at the sys.dm_os_performance_counters, and there I finally found something that looked promising.

 select cntr_value FROM sys.dm_os_performance_counters 
WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)' 

If you add up the 2 total_memory_kb values for the default pool, you get the maximum workspace memory. Woot!

That still didn’t tell me how that particular value was derived, but it gave me something better to search on. And that search returned this article at SQLMag.com which says (emphasis mine):

The SQL Server: Memory Manager object includes the following workspace-related PerfMon counters: Memory Grants Pending, which tells you the number of waiting queries; Memory Grants Outstanding, which counts the number of queries that have grants and are currently executing; Granted Workspace Memory (KB), which measures the total amount of grants SQL Server has made to running queries; and Maximum Workspace Memory (KB), which measures the maximum amount of memory SQL Server is currently willing to devote to workspace-memory grants. In general, SQL Server is able to devote between 25 and 75 percent of the buffer pool’s virtual memory to workspace memory. The value varies based on the demand for memory for other uses (such as database page cache and plan cache). If demand is low, SQL Server devotes a larger fraction of memory to workspace memory needs; if demand is high, SQL Server devotes a smaller fraction to workspace memory.

So there you have it. I feel better, don’t you feel better?

That entire article is worth reading for a better understanding of SQL Server memory. In fact, it helped me put together a bit of code to show me how memory is being used by SQL Server. More on that in a future post (I’ve got to give you a reason to keep coming back, right?).

April 28, 2011 Posted by | General, SQL Tuning, Troubleshooting | , | Leave a comment

Composite indexes – Does column order matter?

When it comes to creating composite indexes, a common bit of advice we’re given is to put the most selective column first.  And I’ve always just taken this at face value.  But recently I got to thinking, is this still true?  I’d never actually tested this theory myself.  Does this rule still apply?

So let’s take a look.  I started by creating a test table in AdventureWorks using existing data.  My goal was to create a table with one high-cardinality column and at least one very low-cardinality column.

USE AdventureWorks;
GO

SET NOCOUNT ON

CREATE TABLE myBigTable (
ID int IDENTITY(1,1),
SURNAME VARCHAR(40),
LOCATION CHAR(3))

INSERT INTO myBigTable (SURNAME, LOCATION)
SELECT LastName, StateProvinceCode FROM Person.Contact CROSS JOIN Person.StateProvince ;
GO

SELECT    COUNT(distinct ID) AS DistinctIDs,
 COUNT(DISTINCT surname) AS DistinctNames,
 COUNT(DISTINCT location) AS DistinctLocs
FROM myBigTable;
GO

Perfect.  ID is unique and location is very low cardinality.  I’ll use those 2 columns in my test indexes.

CREATE INDEX i_highcard ON myBigTable (ID, location);  -- high cardinality first
CREATE INDEX i_lowcard ON myBigTable (location, ID);    -- low cardinality first
GO

Before we go any further, let’s take a look at the physical size of the indexes, just to see if there’s any difference there.

select name, index_id from sys.indexes where object_id = OBJECT_ID('myBigTable')
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('myBigTable'), NULL , NULL, 'LIMITED');
GO

Judging by the fragment_count and page_count values, I’d say there’s no difference in size.  Now, on to query performance.  First we’ll query the table for a particular ID value.  I’ll use the INDEX hint to force the optimizer to use each index.

SET STATISTICS IO ON
SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321

Check out those execution plans.  When we query based on the first column in the index, the optimizer is able to do an index seek.  But when we query based on the second column in the index, as in the second query, the optimizer doesn’t know where to enter the index, so it’s forced to do an index scan.  (In fact, the optimizer even suggests we create an index on ID.)  We can also see the impact of these different execution plans in the IO statistics.

From the first query:
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And from the second query:

Table ‘myBigTable’. Scan count 5, logical reads 9563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That’s a lot more IO the database has to do.  And we all know, when it comes to IO, less is more.

Now, those results were pretty predictable.  But what about when both indexed columns are in the where clause?  Based on what I’ve been told, the index with the high-cardinality column first will be more efficient.  But I want to see that for myself.

SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321 AND LOCATION = '04'
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321 AND LOCATION = '04'

First, the IO stats:

Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Exactly the same.  What about the execution plans?

Also the same.  Even down to the cost.

That surprises me.  I really thought the i_highcard index would be more efficient.  So, I’m back to my original question, when it comes to composite indexes, does column order matter?  And I guess the answer is:  it depends.  SQL Server only maintains statistics on the first column in an index, so having a highly selective first column can be more efficient if you’re using that column in your WHERE clauses.  But if you’re using both columns as filter criteria, it doesn’t seem to matter.  This another reason is why it’s extremely important to know your application and how it accesses the data.

April 7, 2011 Posted by | General, SQL Tuning | , | 2 Comments