Why is SQL Server So Slow?
I’ve been asked this question a few times when looking into performance problems on a website, and the short answer is, it’s not. SQL Server is a very efficient database.
It’s important to remember that a database is just a runtime for your SQL code. It’s just doing what you ask it to do, nothing more, nothing less.
This applies to any database. MySQL, PostgreSQL, maybe even Oracle are all just agents that do your bidding. But I’m most familiar with SQL Server’s tools for finding and fixing some common problems, so that’s what I’m going to talk about here.
Typically what happens is something like this:
- You develop an application.
- You test it. It works okay.
- You add some test data and test it with a “lot? of rows. It still works okay.
- You deploy it.
Then, maybe a few weeks, maybe a few years later, you, or your successor, notices that the application or website’s response is slow, and it turns out, it’s the database’s fault. The conclusion is that the database is slow, and plans are made to replace it.
The problem with this scenario is that the database probably isn’t the problem. The performance characteristics of any of the major databases are all close enough that switching from one to the other without some optimizing in the process would not help. Do that same optimizing to the existing system and you won’t need to switch.
First off, how was the conclusion reached that the database is slow?
Both Windows and SQL Server give you some excellent tools for diagnosing your problem.
First, look at the CPU usage of the SQL Server process.
Either it will be very low, very high, or somewhere in between.
Ideally it will be somewhere in between, but generally low. If it’s pegged at 100% then you’re probably running a query that’s got all the data it needs in memory, and SQL Server is being forced to do something rather inefficient with it, like stepping through all the rows adding the value of some field for example. This can be a symptom of throwing RAM at the problem without optimizing, and will stop being a solution once the data outgrows memory again.
Often what you see, though, is that SQL Server’s CPU usage is very low, but the system’s paging activity is very high. This signifies that the system is starved for memory, and is swapping. SQL Server shows low CPU usage because it’s busy waiting for the disk to deliver some pages.
This screenshot shows a pretty good situation: Just a bit of paging activity going on. If you’re seeing hundreds of pages per second constantly, you’ve got a problem. Swapping will kill your server’s performance.
If you have a query like “select * from users where lastname = ‘bob’” and you don’t have an index on lastname, then what you’re doing is asking SQL Server to read every single row from table users and build a set of matching rows. This is called a table scan. You want to avoid table scans.
This is the #1 cause of SQL Server slowdown over time: Not having the right indexes.
When executing a table scan, the server needs to read every row of the table. This thrashes SQL Server’s cache, so that now other data cached for other queries has been tossed out and now those other queries have to hit the disk. This means even an occasional table scan is a bad thing.
Here’s how to gain a little visibility into what’s causing your problems. First off, launch the SQL Profiler.
This tool lets you spy on what’s going on inside your server. Create a new trace:
The defaults here are good. Hit Run.
While it’s running, SQL Profiler is gathering data on every query your server executes. Launch your application or visit your website and do some of the things that are causing the performance problems. This gives the profiler, and the next tool, the Index Tuning Wizard, some data to work with. When you’ve got a good data sample, stop the trace.
Before using the Index Tuning Wizard, take a look at the result data. The Reads column and the Duration column are two of the most useful: Reads tells you how many disk pages needed to be read to satisfy this query, and Duration tells you how long it took (in milliseconds).
The highlighted line read 296972 pages, and took 29 seconds. Looks like a problem, doesn’t it?
You can use this data to do some manual tweaking; often the problem is immediately obvious. But your next new friend, the Index Tuning Wizard, will help.
Save your current profile run as a Trace File (*.trc), and then on the Tools menu of the profiler, select the Index Tuning Wizard.
It’s best to select Thorough if you’re working with a fairly small data set.
Unless you’re looking for a very specific problem or have a huge data set, select all the tables. The more the wizard has to look at, the better.
With any luck, the next page will tell you that the Wizard is going to fix all your problems.
No such luck in this case: No recommendations were found. But lets look at another example in a different tool: Say you’ve seen one particular query in the Profiler that you’re suspicious of. Launch the Query Analyzer, enter the query, and bring up the Index Tuning Wizard from there.
When you launch the wizard this way, one of the options is to use the Query Analyzer selection as the query to tune indexes for. I typed in a query that’s going to cause a table scan:
select count(*) from eventlog where logportalname = 'bob'
The logproperties field of the eventlog table doesn’t have an index. Here’s what the tuning wizard gives me for this:
Now there’s a nice projected outcome: A 95% performance improvement! A few clicks and you can have SQL Server apply the changes for you, or create a scheduled job to apply the changes later (during a scheduled maintenance period, for example). You can apply the changes to a live database, but queries on the table being indexed will block until the operation is complete, which could take a while.
But back to the case where the wizard didn’t find anything that could help you. Another thing that often happens is that you’ve got a massive table because you wrote code to put things into it, but never to take anything out of it. You create a log table, or an errors table, or a hits table, write code to put things there, and forget about it.
If you see a query or an insert that’s hitting thousands of rows, count the rows in the table.
select count(*) from eventlog
If you see a few million rows, then that’s your problem.
When you insert into a table, there’s work to do, and the bigger the table, the more work. If your eventlog table has a few indexes on it, those need to be updated when you insert, and the indexes on a table with millions of rows are going to take more IO operations to update than inserting into the same table with only a few thousand rows.
The best way I’ve found to manage this is to use SQL Server’s Jobs to automatically clean things up at regular intervals. Check out the Jobs section of the Enterprise Manager:
A job is work SQL Server will do for you at scheduled times. Create a new job.
This is a sample job. It’s got the 3 steps listed that it’s going to take, one after the other, that will drop old data from a database. A job step is just some SQL to execute:
If you’re going to have multiple steps for a job, don’t forget to go to the Advanced tab and set up the job actions – each job should go to the next job when it’s done, except the last one, which should report success.
Set up the job to run at some regular interval, and that’s it – you’ve guaranteed that your table isn’t going to grow unbounded until it starts causing you problems again.
There you go – Hope all this helps! If you have any other SQL Server optimization tips please post them.
January 15th, 2006 at 3:26 pm
Great post! It seems everyone always is quick to establish blame without taking the time to find the real reason for the problem, which your post clearly points out.
I especially like the tip for scheduling index optimizations using the job scheduler!! It really is perfect for this type of use, and SO easy to implement toooo!
I did a bunch of performance tunning and monitoring at a large IT shop in Ottawa. The starting point was to monitor the TIME-TAKEN value in IIS on a week to week basis. The average, minimum and maximum values were charted each week. This allowed me to easily identify the trouble spots, but more importantly to tune the biggest problems first. IIS transactions were further qualified by complexity and usage. You expect a high complex transaction to take longer and if it is low usage, it likely doesn’t warrant any tunning. BUT, any highly complex transaction with high usage should be tunned and maybe even use some non-conventional techniques to make it high performing. Hopefully they are solved with index tunning and reports from SQL Profiler. A high dependancy on CRUD stored procedures is practically a guaranteed path to performance problems, once the application has been in production for a while.
Tunning has to be one of the most exciting parts of a project and I find it difficult to understand why it is sometimes ignored.
January 29th, 2006 at 5:00 pm
hey man,
MS SQL is very very slow, it really sucks, try to run MySQL on Linux (Gentoo/Linux), and you gonna see what does ‘performance’ really mean.
April 4th, 2006 at 3:11 am
Thanks for sharing this in detail. It has just helped solve my application issue when after 2 months the SQL Server becomes really slow. THANK YOU VERY MUCH!
April 12th, 2006 at 10:47 am
A detailed explanation about what to lookup when you’ve a performance problem, and more I appreciate with GUI representation for a newbie to understand the process.
A valuable information for new starters and experienced ones too.
-
Satya
SQL Server MVP
May 17th, 2006 at 10:18 am
Is there any way to pin a table to a certain number of rows, I am trying to clear down a table with 7 Million + rows, and I can’t run any command on it, – it just hangs.
October 8th, 2006 at 2:04 pm
Thank You !
I was ordering a 7000 rows table by a non indexed time column .. :))
Now from 4s/query –> 30ms
October 25th, 2006 at 9:53 am
“MS SQL is very very slow, it really sucks, try to run MySQL on Linux (Gentoo/Linux), and you gonna see what does ‘performance’ really”
I have used mySQL but found that it’s lack of stored procedures and sql functions make it slow. If you want to see performace, use SQL functions to select your data, and stored procedures to do the rest. Indexing is very imporant. Great article.
March 5th, 2007 at 7:05 pm
” “MS SQL is very very slow, it really sucks, try to run MySQL on Linux (Gentoo/Linux), and you gonna see what does ‘performance’ really”
I have used mySQL but found that it’s lack of stored procedures and sql functions make it slow. If you want to see performace, use SQL functions to select your data, and stored procedures to do the rest. Indexing is very imporant. Great article.”
Please note the line…
“This applies to any database. MySQL, PostgreSQL, maybe even Oracle are all just agents that do your bidding.”
It is quite true. I run MySQL, SQLServer 2000 and SQLServer 2005. But as stated the databases are all very similar and what it really comes down to is the code you write/run. Indexes are key, there are other things like triggers and cursors that can kill you as well, but indexes will help you out a lot. If you are having problems with MySQL on linux on the same box that you had an MSSQL installation going and its slower, you could look into your kernel configuration for potential performance boosters there (2 processor system only running 1 processor? Only utilizing half your ram?)
Thanks for the great article.
May 1st, 2007 at 1:17 pm
Thanks so much!!! We’ve been having slowness issues with our SQL server for the past 4 years. This info. helped us increase our SQL performance tremendously:)
August 28th, 2007 at 8:23 am
I was having a problem on a specific table, this post helped me eliminated that problem, thanks very much!
October 29th, 2007 at 6:27 pm
I had also some problems performance with my MS SQL Database – in my case reason was the jdbc driver (Java 1.4.1) and now, together with Java 1.5 no problem anymore. Does M$ know, if you access a M$ db with jdbc??? I am really shocked now.
January 28th, 2008 at 6:06 am
Thanks!
This helped me improving in 99% my database, and boosting up the speed!
February 18th, 2008 at 1:50 am
I have millions of records in 5 tables of my database, and found that delete takes most of the time. I takes 30 mins to 1 hrs for deleting a set of records for a where condition. it doesnt have any indexes on it except the primary key.
But i need the data, and i dont want to delete the data in the table and want a performance improvement while deleting,selecting and inserting.
Can anyone help on this?!
June 30th, 2008 at 5:46 am
I have millions of records in 5 tables of my database, and found that delete takes most of the time. I takes 30 mins to 1 hrs for deleting a set of records for a where condition. it doesnt have any indexes on it except the primary key.
But i need the data, and i dont want to delete the data in the table and want a performance improvement while deleting,selecting and inserting.
Can anyone help on this?!
October 20th, 2008 at 4:10 am
Hello to all,
I am db-administrator and I have a general but critical question: WHY CPU USAGE DOES NOT REACH THE 100% percent for both processors in the machine of SQL Server 8? Is it Window 2003 Server policy or Microsoft Sql Server prefers to keep cpu usage not to max rates for both processors. I think by this way we gain a lot of delay since the queries should be finished execution if they had been used by the 100% of cpu??
thanks in advance,
JohnnyC
October 20th, 2008 at 7:23 am
How busy is the disk? If the CPUs are waiting for data, then they don’t have any work to do.
For example, if you have a table with a million rows and no indexes, doing a “select foo from mybigtable where bar=3″, the database is going to have to read every row of the table to execute this statement.
Check your indexes. If they look OK, make sure you have enough RAM because swapping would be another cause of the CPUs being idle during the execution of a statement.
February 15th, 2009 at 8:55 pm
Thanks for your wonderful info. However, my issue is a bit different. I wanted to benchmark 3 servers that I have. They have the same os (windows 2003) and MS SQL 2005 standard. My program is to insert 2 million records into a empty table. My cheapest server completed in 12 minutes, my medium server completed in 11 minutes. My most expensive server (Dual-core 2GH CPU, 4GB RAM, 2 X 500GM HD with RAID1 config) took 2 hour 40 minutes to insert 900,000 records. I had to kill the job. I benchmarked this server with others on disk speed and memory, nothing seems to be out of ordinary. Hence, I suspect that my MS SQL configuration maybe a problem for this server. But, I dont’ even know where I should start looking. Can you shed some lights? I’d really appreciate it.
February 18th, 2009 at 1:26 pm
hi,
i am using 50 database in a single server and in each server i am scheduling the jobs to be run for each database every 1 min.I found in some of my jobs the job has been delayed it takes 3 to 5 sec more than the time i have set, so this cause me the problem.since if one job is success then only i can proceed the next step of my process if it delays then there is problem for me.What is reason for this delay in the job.How to over come this.
In each database some 50(min) to 500(max) users will be there
March 27th, 2009 at 10:46 am
Use MySQL its faster – are you joking? I’ve had so many problems with MySQL (on Unix), that I setup a SQL Server Box to test. The tables are the same, the indexes the same. MySQL set to Innodb, pools enlarged. And inserts into tables with over 8 million rows are 10 times slower in MySQL. I gave up on measuring Deletes – several hours to delete 1 mill rows (using a PK in the where clause) on MySQL. VS several minutes to run the same delete in SQL Server. By the way my MySQL Box is 8 core 32 gigs of ram running Ubuntu, the SQL Server box is 4 core 4 gigs of ram running Windows 2008.
March 31st, 2009 at 1:24 pm
Good article. However I want to point out what is a HORRIBLE cause of slowdowns using SQL Server by Java programs and the problem and solution are little known. The MS JDBC driver + db engine does one thing that causes most of the indexes to be IGNORED. If the database is not unicode (many MS databases are Latin-1 or MS Latin-1) and you have indexes on String/VARCHAR columns, they will be IGNORED when doing most JDBC calls. Why? Java passes over a UNICODE String, something on the MS side looks at the column, sees that it is not UNICODE in the database and then will not use the index. This causes horrible response problems since not all indexes are on NUMBERS — compared with other databases that do not do this. (Oracle for example will handle this fine even if the database is Latin-1.) There is a workaround — for Java people add this to your URL. Suddenly SQL Server will fly. Add the sendStringParametersAsUnicode=false to the URL.
jdbc:sqlserver://myhostname;databaseName=mydatabasename;sendStringParametersAsUnicode=false
April 5th, 2009 at 6:28 am
Very good contents. Couple of things first I go into describing issue – 16 GB ram (10 gb allocated to primary instance of sql server), Database size – 40 gb, SAN disk ( lun, metalun), W2K3 64bit and MS SQL 2K5
This is SSIS package that is installed and runs on SQL sever. This job runs great and have been benchmarking to 2-3hr of processing time. Some and I am stumped that sometime the same job takes more an 9 hr to complete keeping all constant like volume of data day over day and week over weeks. I graphed PLE and disk graph – they don’t gain me anything solid as I couldn’t find any correlation. Over a period of time we disabled McAfee during processing and see no effect in processing time.
Anything that you guys can point that I can take back to my team and look for indicators…
April 21st, 2009 at 10:44 am
I am having an issue more specifically related to the SSMS itself. It is like there is some 5 to 10 second delay on almost everything in there now. So I pull up a list of the tables in the database and that is fine. But when I right click the table, it may take 5 to 10 seconds for that menu to show up. So then clicking Open Table or Modify may take another 5 to 10 seconds. If I try to run the query to pull back the data, I get the Executing Query. Waiting for response from data source message for another 5 to 10 seconds, then the query runs. It may just be one record in the table, it doesnt seem to matter, it still has that lag. Now I can take that same query and open up a New Query window and it will run right away. Something just seems to have started causing this lag for everything else in SSMS and I can not for the life of me figure out what it is. I saw some posts about turning off online help but that didnt do anything for me.
May 19th, 2009 at 3:46 am
Thanks a lot to the author who writes this block.
This is really a very good documents..
Thanks….
January 21st, 2010 at 6:37 pm
Dan, I have the same problem with SSMS and haven’t figured it out either. Pretty much only happens when I access a remote server over a VPN/WAN connection but it didn’t used to be so slow. Now I just RDP to the server and use SSMS there.
February 4th, 2010 at 9:43 am
I want to know why is it that, once its afternoon, my server which is 128kbps both down and up stream will be so very slow. this is very disappointing and it makes my job so frustrating. My facilitator kindly help me out or any other resource fellow around. I will really appreciate if I can have the reply in 24 hours.
August 9th, 2010 at 11:31 am
Thank you to the author. You saved me a lot of headaches and grief. Your recommendations worked perfectly and I am up and running again. The end users are extremely pleased and app appears as if it running better than it ever did.
December 20th, 2010 at 6:36 pm
[...] Steve Tibbett wrote a blog post a few years ago on this from the SQL server side, entitled Why Is SQL Server So Slow?. He gives some tips on improving Microsoft SQL Server performance, but his basic point is [...]
July 14th, 2011 at 12:56 am
I suggested to our database guys that maybe we should index more fields to improve query speed on our MySQL database (which shares a linux server with another MySQL database). However, thei comment was that this will increase the overall load on the server. Is this true?
August 5th, 2011 at 10:59 am
I have to disagree to be honest.
Microsoft SQL Server is slower than its rivals.
Oracle and Postgres are much faster databases systems.
Also, the index tuning wizard is a waste of time, because it always comes back with syntax errors, and 0% performance increase predictions.
waste of my time.
thanks for your article though. good job. just a bad product.
September 2nd, 2011 at 7:18 am
Unindexed queries will increase the load on the server much more than the work of maintaining the indexes.
It’s a tradeoff. If you have a table that you mostly write into, and only occasionally query, then it makes sense to leave it unindexed and deal with the occasional slow query. But if overall performance is suffering because your database is scanning tables then it’s time to index.
October 6th, 2011 at 1:36 am
This one of the best articles I found. After spending a lot of time googling for a solution. Well done!
February 15th, 2012 at 6:29 am
Has anyone found a way to force MSSQL to process WHERE conditions in specific order? I tried even a nested select, where the outer query performed filtering of the initial data – no chance, SQL server was “smarted” and reordered conditions to something unusable. The problem: there are conditions that limit result set to some relatively constant size (say, 1 day out of 365 days of event logs) and a condition for “serialnumber is not NULL” or something like this. The damn SQL Server evaluates the last condition first – and while it is indexed, it holds true for 99.9% of data. Any help on this?
February 15th, 2012 at 11:28 pm
I migrated from sql server 2005 on an older box to 2010 R2 today both running windows7. The access app I am running locally is a good order of magnitude (10 times) slower at the least. I went from Access 2007 to 2010 also. I imported the database from a backup. It’s just plain slooooooo. Any ideas?
May 23rd, 2012 at 10:00 am
Excellent Post!!.
My new Best friend the Index Tuning Wizard !!!! This post just saved me 6 hours of processing daily!!
Clustered index instead of regular Non-Clustered index ….. no why didn’t I think of that.
July 15th, 2012 at 3:58 pm
Hi everybody!!
nice article but I have a big problem, I’ll explain you, I have an app in .net, this app have to run some stored procedures. I have detected that one sp lock a table, already solved that problem, but now other sp lock the same table. Is it possible that the problem is a part of the data base? Please I need your help!!