SQL Server running very slow

My SQL Server is running incredibly slow.  I took a screenshot of the Activity Monitor:

 Activity Monitor
hrolsonsAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
hrolsonsAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
hrolsonsAuthor Commented:
I'm not sure what you mean by "profiling your server"?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
hrolsonsAuthor Commented:
I don't seem to find the Profiler.  I wonder if it is only in the paid version?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
hrolsonsAuthor Commented:
I'm going to purchase the paid version today.  Trying to figure out which version to get.
0
 
hrolsonsAuthor Commented:
OMG, over $7000, perhaps not today.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.