Sql-server – How to rule out SQL Server as the reason for a slow website

sql server

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?

Best Answer

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.