Hi, i am a new to DBA category, In our Concern we are using Sql server 2005, Our management decide server is too slow. So they decide to upgrade or replace the server. So how can i assessment of the server and what are the reports can i take from server? how can i know the server is fast or too slow? what are the steps to follow?
I'd say there are two approaches to this. First you need to learn about the holistic approach to server performance - what are the things that can affect the speed of a databases server : RAM, disks, cpu, other applications on the server, number of instances on the server. Then there's the specifics. What is 'too slow'? What has given management the perception of 'slowness'? Is it a particular application or report. What queries underpin that? Look at the individual query performance, together with the knowledge of the whole (from above) and look into execution plans, indexing strategies. A recent question asked a similar thing:
which edition of SQL you are currently using 32 bit or 64 bit what is the performance of your drive(SAN) check for missing Indexes. create perfmon counters for CPU--system processor queue length Memory--available MBytes Disk--read/sec,writes/sec
Why do they think it's too slow? If it's just a few specific queries or reports, then perhaps optimise those. If it's general performance, on the other hand, then look at Ramesh's suggestions. Is the server a dedicated SQL Server, or are there any other processes running on it?
how can i know the server is fast or too slow? - User complains about the performance of database like queries or sp takes long time to complete than usual. - You may see high resource usage on the server like CPU, Disk, Memory. - Performance counters shows high values when compare with your baseline. I would suggest you to capture the performance counters(CPU, Disk, IO, SQLStats etc) wit SQL Serer profiler trace, correlate te
results.By doing this you can relate the query resource usage with the Performance counter values, Identify the expensive queries, analyze the cause of performance issues, possible common issues are missing indexes, outdated stats, high waits etc, these common issues always results in high resource usage. Find the expensive queries and find tune them. Above points are few common troubleshooting techniques, apart from this your server must be configured properly example, Disk level (Raid) configuration, Memory Configuration (differs for 64bit and 32bit), File placement, tempdb config etc...
There are generally three aspects to performance in SQL Server. First is the hardware you're using, the speed of the CPU, the amount & speed of your memory, and the configuration of disk drives you're dealing with. Then you have to deal with the configuration of your operating system and SQL Server itself to ensure it's set up correctly. Finally you have to get the internals of your databases, the constraints and indexes and the queries you put together. In short, there's way, way too much information needed to get you started through a simple answer to a forum post. The very best thing you can do at this point is hire a consultant who has the knowledge you need to come in and get it done. Barring that as a solution, you'll need to learn how to do this yourself. I'd suggest reading the book Troubleshooting SQL Server for the Accidental DBA. It's by Jonathan Kehayias and Ted Krueger. It's available as a [free download] or you can buy the paper book. This will give you a good boost on figuring out your system configurations and basic performance. To get more information on the database internals for performance, I'd suggest my [book on query tuning]. It's available from Amazon. Those can get you started, but this is a seriously dense topic. :