?
Solved

SQL Server running very slow

Posted on 2011-10-11
11
Medium Priority
?
227 Views
Last Modified: 2012-06-22
My SQL Server is running incredibly slow.  I took a screenshot of the Activity Monitor:

 Activity Monitor
0
Comment
Question by:hrolsons
  • 5
  • 5
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36952572
hrolsons,

Is this slowness constant or during specific times of the day? There could a host of reasons for the slowness starting with poor queries that are requiring more I/O than necessary. If you can pinpoint a correlation to times of stress versus your baseline, then it may make things easier. In other words, what is the servers typical state? What are the hardware specifications? What edition of SQL Server 2008 are you running and how many instances and databases per instance are running on this server, etc.

Is the data on local hard drives or SAN?

Please provide a little more details from any troubleshooting you have already done into the issue and we can go from there.

Kevin
0
 

Author Comment

by:hrolsons
ID: 36952634
I didn't want to muddy the water too much, but here goes:

The only reason I have SQL Server 2008 is to store data for my eBay Blackthorne software.  

When things start running slow I can usually do a db repair in Blackthorne and things will run better for a very short time and the symptoms come back.  The db is only used by a single computer and is located on the hard drive.

Hopefully that gets us started in the right direction.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36952690
If running a database repair within the application that suggests that either its indexing or method of storing data is inefficient and they have tools in place to "cleanup" the applications mess. You can check for the former, using something like written about here: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Another reference:
Finding Missing Indexes
http://msdn.microsoft.com/en-us/library/ms345417.aspx

The root cause may be the hardware is not sufficient for the level of activity you have with this particular instance of eBay Blackthorne or as stated poor performance of the application itself. I have no experience with eBay's Blackthorne to say with any authority. Just my gut feel having worked with applications where scheduling IIS to restart every weekend is good maintenance or the application had tools specifically to speed things back up. Those make me highly suspicious of poor development. i.e., to cover memory leaks, reboots (clearing memory) or other workarounds is done instead of properly coding to release resources. Similarly, having tools to archive or clean out data when a table gets too much rows is wonderful, but if this is just to cover poor indexing on the table then it really doesn't fix the root cause just buys you time.

Soon what I found in my case is that eventually the load or data needs grew faster and faster as time progresses, so now server was always being rebooted or data is always being archived.

Anyway, try profiling your server while running the db repair to see what exactly the application is fixing. That may speed up your troubleshooting process to find the table(s), queries, or other cause(s) to your issue.

Kevin
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:hrolsons
ID: 36953495
I'm not sure what you mean by "profiling your server"?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36956396
SQL Profiler is a tool provided with SQL Server. It is not necessarily always the best tool for performance tuning, but in a scenario where you do not know what is going on, I think it is useful as it allows you to gain insight into the statements being executed, the CPU and IO associated, etc.

Tim always explains things well, so try this:
http://www.techrepublic.com/blog/datacenter/capture-execution-plans-with-sql-server-2005-profiler/269

Here is the BOL also: http://msdn.microsoft.com/en-us/library/ms187929.aspx

Kevin
0
 

Author Comment

by:hrolsons
ID: 36956896
I don't seem to find the Profiler.  I wonder if it is only in the paid version?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36957494
Hmm. I was wondering that when I first posted, which is why I asked what edition. So you are on Express Edition. Note that Express Edition has limitations. If you are on Express, you will be bound to 1CPU and 1GB RAM, so you can have heavier IO for paging or just the fact that the limited memory cannot handle your full data set.

SQL Edition Comparison
http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

Yes, SQL Profiler is not in Express Edition. I don't believe the Advanced Services install adds it either, but you can try.
0
 

Author Comment

by:hrolsons
ID: 36957591
I'm going to purchase the paid version today.  Trying to figure out which version to get.
0
 

Author Comment

by:hrolsons
ID: 36957664
OMG, over $7000, perhaps not today.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36958936
Ha. Yes it is not a trivial cost. Depending on your needs, Workgroup Edition may work as it is closer to $700 than $7,000. However, if Express meets your needs, you just need to figure out what the eBay clean db tool is doing some other way. For example, you can take a snapshot of the data before the db tool is run and one after and see what is different. I suspect it is archiving or cleaning up some records, but again I have no experience with that application so no proof. Therefore, you will need to investigate that for yourself. If it is a paid tool from eBay, see if they provide support that you can ask about it. Also, they may have system requirements that state whether Express is capable of running the application productionally.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959701
MS SQL Server Express does not support SQL Server Profiler.  See here for more information:
Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993.aspx
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question