Sql Server is Slow - No idea what we are doing

[Jeff Thompson]

Our SQL Server runs slow. Being small we don't have an SQL Admin, requiring us application dev's to figure stuff out. One query we have takes 22 seconds to return 1700 queries. The query is as optimized as we can get it. The server on the other hand needs some love.

We don't utilize indexes and have never used profiler or any analysis tools before. I'm looking for a simple tutorial on "When your server is slow.... do these steps" kind of thing.

Anyone have any thoughts or resources to get me started on the road to optimizaion?

[Erland Sommarskog]

Post by Jeff Thompson
Our SQL Server runs slow. Being small we don't have an SQL Admin,
requiring us application dev's to figure stuff out. One query we have
takes 22 seconds to return 1700 queries. The query is as optimized as we
can get it. The server on the other hand needs some love.
We don't utilize indexes and have never used profiler or any analysis
tools before. I'm looking for a simple tutorial on "When your server is
slow.... do these steps" kind of thing.

I sense a contradicton here "as optimized as we can get it" vs. "we don't
utiliize indexes".

There certainly things that can be done with the server itself, like setting
maxdop, configuring memory, review hardware etc. But if your queries are
not well-written and/or there are no usable indexes that will only help so
much.

Getting this sorted out on your own with no performance-tuning experience
is not exactly easy, and in the end it is a matter of hard work - even if
you know the tools and understand indexing.

Here are a couple of suggestions for you, with different budget in terms
of money and man-hours.

1) Convince management to engage an SQL consultant that can come to your
site and mentor you. If that person works with you for say two weeks, he
or she can address some of the ugliest queries and teach you how to find
the next guys and some ideas how to tackle them.

2) Use the Database Tuning Advisor. With this tool, you record a full day
of workload, and the tool will then recommend you which indexes to add.
It is important that what you record really is a representative workload.
This tool is heavily abused, because people only feed it a single
query. Then they get an index for that query, and people end up with lots
of similar and redudant indexes.

I have actually never used the tool myself, as I prefer to run my
own traces and analyses. But I believe that for a site with low
experience, the tool can do a decent job *if used correctly*.


3) This book:
http://www.amazon.com/Troubleshooting-SQL-Server-Guide-
Accidental/dp/1906434786/ref=sr_1_1?ie=UTF8&qid=1457259009&sr=8-1