Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related.

What steps or procedures do I take to confirm or rule out that SQL Server is the reason for the slow load times? Also, without a baseline, what may be behaviours/characteristics of SQL Server to look out for that could indicate that there is a problem?

share|improve this question

5 Answers 5

I'd suggest you need a more definitive problem statement - is it all web pages? If it's just some, what are those web pages doing? What is the acceptable load time, and what is the current load time? Is it all users, or just some?

I used to be a web admin, and my first check would have been the webserver - is memory and CPU ok? Do the OS and web logs show anything? Can the scripts output a log to show what's going on? eg I used to put the database stuff in one section, and write times/durations for each database activity to a log, when activated.

If you need to check the SQL Server, check the SQL & NT logs, and again check the memory, disc IO, and CPU - task manager will give you an ideal of two of these (not disc IO). Ideally, SQL Server should be on a dedicated box rather than shared with anything.

Whilst someone is using the website, look at either Activity Monitor or sp_who2 - are you able to see the query dawdling? Is there any blocking? Keep refreshing... if the query is runnable/suspended/running for a while, then you need to find why it is slow. If you can't see a query in those states, it may not be SQL Server.

share|improve this answer

First, fire up the Activity Monitor. What do you see in Overview - are lots of sessions waiting? Now look in Resource Waits - is the server struggling for disk I/O, or is it spending a lot of time waiting for locks. You can also see recent expensive SQL statements. For more detail you can look at the Activity section, or fire up SQL Profiler (with the caveat that profiling itself is an expensive activity - don't make a bad situation worse!).

There is a lot more to performance tuning than this of course - but this will quickly show you what SQL Server is actually doing. And one important fact to remember: the database will try do whatever work you give it, but it can't always guess your intent. If the app is sending bad SQL (e.g. SELECTing many more rows than it needs and filtering them in the app, I have seen this many times) or the schema design is bad (e.g. missing indexes) then the fix for the perceived database problem lies outside the database. The Tuning Advisor can help you diagnose this kind of issue.

share|improve this answer

A quick way is to fire up profiler and look for queries that take over a couple of seconds to run. Then look at the waits which are being logged to see what the cause of the long running queries are.

share|improve this answer

This handy stored procedure will tell you what is happening at this very millisecond on your database server:

SQL 2000: http://cookingwithsql.com/source/sp_now.sql

SQL 2005 and up: http://cookingwithsql.com/source/Now.sql

I wrote a detailed description of "sp_now" here: http://www.sqlmag.com/article/tsql3/prove-that-the-database-isn-t-to-blame

share|improve this answer

When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth. — Sherlock Holmes

For the sake of completeness, consider proving that everything else can't be the problem. Eliminate hardware, including RAM, NICs, routers, cables; eliminate server processes; eliminate other software, like the web server, proxy server, etc.

For a lot of organizations, just figuring out what "everything besides the database" is can be quite an eye-opener.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.