Robert's SQL Blog

Recently I was working on a situation where an application performed remarkably slower on SQL 2005 than it did on SQL 2000. Of course this doesn’t make any sense. SQL 2005 is a big improvement over SQL 2000 and the query optimizer is more optimized. Unfortunately this is not always the case. SQL 2005 and beyond are better but sometimes they make decisions different from SQL 2000 causing longer execution times.

The question is, is this due to SQL or is it bad database design. Well, I leave that discussion for another time and will provide you with a link demonstrating a situation where SQL 2005 is slower than SQL 2000. http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/14/performance-of-a-query-on-computed-column-can-degrade-in-sql-2005-with-the-increase-in-complexity-of-function-in-computed-column.aspx

Now in the case I was working on was a situation where after the upgrade from SQL 2000 to SQL 2005 an update took 12 minutes where it used to take a couple of seconds. The funny thing however was that this behavior was not experienced on the test system. This is an identical system, the database is a copy of the production site and the number of concurrent users was the same.

With a profiler trace the end-user experience was confirmed. The trace showed a stored procedure on production which took 12 minutes. I searched the same stored procedure in the trace file from the test system and it showed 4 seconds. More interesting was the number of reads on both systems. In the test system the stored procedure did around 200,000 reads and on the production system 40,000,000. That’s a whopping 200 more. One of the things you have to ask yourself with performance tuning is, is this execution time normal for this load? Well, 12 minutes for reading 40M records on a virtual machine seems right. At least it comes near the 4 seconds times 200, which is 800 seconds. Around 12 minutes. So, conclusion one: The execution time is correct. The next step is to check where the excessive reads come from.

Normally you would see this in a showplan right away. This stored procedure did around 70 individual updates (all with subselects like ‘where @id in (select …..)’) and a lot of before-insert and after-insert triggers. Even on a full HD monitor you couldn’t make out the plans easily. I decided to take a different approach and use the showplans as a last resort. One of the things you should do as a best practice after upgrading a database is perform an index rebuild and a full statistics scan. I did this on the production site and the performance did not change. I cleared out the procedure cache and tried it again but the results we’re the same. I did the same on the test system with a remarkable result. After rebuilding the index, full statistics scan and clearing the procedure cache the query took 12 minutes on the test system as well. A logical explanation would be that the cached plan on the test system was different than the one on the production system,causing less reads. The customer didn’t know what they tested before so I wasn’t able to reproduce this to see if that resulted in a different showplan. But then again,I’m quite sure it would under certain conditions.

Now I knew it for sure, the excessive reads we’re coming from a missing or wrong index. Using the dmv from Bart Duncan’s blog (http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx) I created a list of index candidates. Since this wasn’t our application I wanted to investigate some more to make sure the indexes would be beneficial. I contacted the ISV and asked if it would hurt if I ran the query from SSMS. They indicated that this shouldn’t be a problem. So by adding SET STATISTICS IO ON I ran the query. This showed two tables where more than 20M reads we’re done. When I checked the table both of them together did not include more than 100K rows. So, 20M reads on 100K is a lot, more than needed. From the missing index query I took the indexes that involved these two tables and placed these. I ran  the stored procedure and it got from 12 minutes to 3 seconds. That seemed fair for now. After two full tests with users no performance degradation was noticed and I could call it a day.

Moral of the story is that making your application run on SQL 2005 or 2008 is more than checking the upgrade advisor. SQL 2005/2008 do have a newer/better/smarter but different processing engine. The differences could be minimal, but a difference of 1 second could have a huge impact if it involved thousands of users. Do not just test of your queries work but also test if the number of reads are at least equal, compare showplans if possible.

Dinsdag 14 december j.l. waren we te gast bij Microsoft op Schiphol waar Andre Kamman en Paul van Wingerden een tipje van de sluiter oplichten van SQL Denali. Ook deze avond was weer goed te noemen. Enige minpunt was het aantal afwezigen. Of iedereen stond in de file of een aantal (een groot aantal) had maar voor de zekerheid gereserveerd. Dit is heel jammer want er wordt wel geinvesteerd (catering, locatie) en mensen die wel willen komen worden teleurgesteld. Bij deze dan ook een oproep aan eenieder om je af te melden als je toch niet komt of je helemaal niet aan te melden als je nog niet zeker weet of je komt. De Pass organisatie draait op vrijwilligers en sponsoren die hun vrije tijd hierin steken.

Sinds september is er iedere maand een goed bezochte Pass avond geweest en de tweede editie van SQL Zaterdag. Tot nu toe zijn de onderwerpen en sprekers voor deze avonden en SQL Zaterdag door PASS NL gekozen en benaderd. Maar PASS NL is er voor de gebruikers van SQL Server in NL (en omstreken). Heb je een onderwerp waar je graag meer over wilt weten dan horen we dit graag. En als je ook nog een geschikte locatie weet dan is dat helemaal mooi. Maar natuurlijk mag je ook je ervaringen delen met de PASS community. Of wellicht wil je graag een probleem voorleggen of een discussie avond.

Na twee succesvolle SQL Zaterdagen kan een derde niet achterblijven. Ook hiervoor zijn onderwerpen nodig. Verzin het, het kan haast niet te gek. Ook als je andere ideeen of verbeteringen weet laat het weten.

I used to work for a government agency, and not just a regular one, but a military one. So, as you can imagine security is a big issue. The same goes for financial and medical databases. For some people downtime is not an option, for others the integrity and liability of the data is the most important.

SQL Server helps you on this. It’s followed the path Microsoft took a while ago to make their products ‘secure by default’. This means the Windows Firewall is now enabled in the latest server releases, instead of disabled or absent in earlier releases. The same goes for SQL Server. No more Builtin Administrators in the SYSADMIN serverrole. And xp_cmdshell is disabled by default. As is the guest user in user databases.

I’ve seen a lot of developers turn pale white when their glorious application didn’t work in production, simply because it needed SYSADMIN. Well, it didn’t need SYSADMIN but they forgot to specify what permissions were needed and did not provide a databaserole with the proper permissions. If you do not have a strong organization it’s most likely you will give the application account SYSADMIN permissions. You might believe that some day you will change this to a databaserole but that day will never come.

Sometimes, and only sometimes and in small companies, they place the SQL service account in the Domain Admin group (yes, people who do this exist on this planet). And to be honest, if you are a DBA,this is the best thing that can happen to you. Because now you enable xp_cmdshell and with a simple ‘xp_cmdshell ‘dsadd user cn=backdoor,cn=users,dc=contoso,dc=com -samid adm‘’ you can add users to AD. But of course you can grant yourself Domain Admin permissions. And you don’t have to impersonate the account because xp_cmdshell always runs in the context of the SQL service account.

And if there are people who believe the SQL account has to be Domain Admin to be able to register a SPN at startup; Read this: http://msmvps.com/blogs/ad/archive/2010/07/15/how-to-delegate-the-right-to-delegate-kerberos-constrained-delegation.aspx

And any domain administrators who goes along in this and grant Domain Admin permissions to a service account should URALT (Update Resume And Leave Town).

SQL Server MVP and fellow dutchman Andre Kamman provided a script on his blog (http://andrekamman.com/find-sql-servers-on-your-network-with-powershell) to enumerate SQL Servers in your network. A great script indeed but I needed some more information. I needed the connection information as well to be able to connect to the instance and I needed to know if the instance is running.

So I tweaked Andre’s script and changed it to fit my needs. Also it returns objects now. I thought I put it up here so it might be of some use for anyone else.

I can also tell this is part of the next version of the SQL Monitoring Tool. The progress is coming along nice and I hope to put up a beta release by the end of this year.

$instances = @()
$hostName = [System.Environment]::MachineName
$ErrorActionPreference = "SilentlyContinue"
$reg = $null
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $hostname)
$regkey = $null $regkey = $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL")
IF ($regkey)
{
    foreach ($regInstance in $regkey.GetValueNames())
    {
        $regInstanceData = $regKey.GetValue($regInstance)
        $versionKey = $reg.OpenSubKey("SOFTWARE\MicrosoftMicrosoft SQL Server\$regInstanceData\Setup")
        $version = $versionKey.GetValue('PatchLevel')
        $edition = $versionKey.GetValue('Edition')
        $instanceName = $hostName
        $serviceName = $regInstance
        $clusterKey = $reg.OpenSubKey("SOFTWARE\MicrosoftMicrosoft SQL Server\$regInstanceData\Cluster")
        if($clusterKey)
        {
           $hostName = $clusterKey.GetValue('ClusterName')
        }
        if($regInstance -ne 'MSSQLSERVER')
        {
            $instanceName = "$hostName$regInstance"
            $serviceName = "MSSQL$$regInstance"
        }
        $serviceStatus = Get-Service -name $serviceName
        $instance = New-Object System.Object
        $instance | Add-Member -type NoteProperty -name Name -value $instanceName
        $instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
        $instance | Add-Member -type NoteProperty -name Version -value $version
        $instance | Add-Member -type NoteProperty -name Edition -value $edition
        $instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
        $instance | Add-Member -type NoteProperty -name InstanceID -value $regInstanceData
        $instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
        $instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
        $instances += $instance
     }
}
$regkey = $null
$regkey = $reg.OpenSubkey("SOFTWARE\MicrosoftMicrosoft SQL Server")
IF ($regkey)
{
     foreach ($regInstance in $regkey.GetValue('InstalledInstances'))
     {
         IF ($regInstance -eq "MSSQLServer")
         {
             $versionKey = $reg.OpenSubKey("SOFTWARE\MicrosoftMSSQLServer\Setup")
             $serviceName = $regInstance
         }
         ELSE
         {
             $versionKey = $reg.OpenSubkey("SOFTWARE\MicrosoftMicrosoft SQL Server\$regInstance\Setup")
             $serviceName = "MSSQL$$regInstance"
         }
         IF ($versionKey)
         {
             $version = $versionKey.GetValue('PatchLevel')
             $edition = $versionKey.GetValue('Edition')
             if($version)
             {
                $instanceName = $hostName
                $clusterKey = $reg.OpenSubKey("SOFTWARE\MicrosoftMicrosoft SQL Server\$regInstanceData\Cluster")
                if($clusterKey)
                {
                    $hostName = $clusterKey.GetValue('ClusterName')
                }
                if($regInstance -ne 'MSSQLSERVER')
                {
                    $instanceName = "$hostName$regInstance"
                }
                $serviceStatus = Get-Service -name $serviceName
                $instance = New-Object System.Object
                $instance | Add-Member -type NoteProperty -name Name -value $instanceName
                $instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
                $instance | Add-Member -type NoteProperty -name Version -value $version
                $instance | Add-Member -type NoteProperty -name Edition -value $edition
                $instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
                $instance | Add-Member -type NoteProperty -name InstanceID -value $regInstance
                $instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
                $instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
                $instances += $instance
            }
         }
    }
}
$instances | Select-Object Name, DisplayName, Version, Edition, InstanceName, InstanceID, ServiceName, Status | where {$_.Status -eq 'Running'}