hi team, we are using db of 200 GB with 100 users connected to the server out of the 30% of people say that sql server is slow,i have conducted a sutver to these 100 people, survey say's that only 30% of users are slow, how to start ,plz help me
A few ideas: Check to see if there's a long-running query blocking some table that's required by those users. Use the Trace tools to analyse the query load that those particular users are running. See if there are any common features, eg particular table or stored procedure being referenced by those users. If it's a particular SP, then analyse that to see why it's slow. If it's a table, then look at the queries referencing the table to see if there's a missing index. Something you will need to do is get a baseline of the performance of the server, so that you can tell if the server really is running slowly, or if it's a specific issue.
This is an open ended question. Performance depends on the concurrent users using SQL Server and also on the Hardware available to SQL Server. The configuration of SQL Server should make use of the Hardware available to the best possible extent.
**I would** - Find out which are common areas of the application that users a saying "its slow". - Are the reports of "its slow" at the same time of the day. - **Profile** (as @Thomas has mentioned) the queries that are been executed (im lucky we write the s/w do i know which app is using which proc). Find the long running queries and review the **execution plan**. - **Monitor** things like "buffer cache hit ration", "page life expectancy", "SQL comps/sec", "SQL re-comps/sec", "lock waits", "disk dueue length" (to name a few) through **perfom**. - Check if there is any blocking with any of the queries running **My quesitons to you...** - What is your **hardware** setup (CPU, Memory, Disk setup (number of driver, raid etc), network)? - Are you using **x32 or x64** Windows & SQL? - What **index optimization** are you doing (ola has a excellent script -
http://ola.hallengren.com)? - Are **statistics** getting updated? Hope this help
To add to the good advice so far, please understand that you need a baseline and a qualified statement of a problem to work with. If you have a baseline for your system (CPU, Disk, Network, Memory, # of Connections, Trans/sec etc.) you know what "normal" is for your system. If you know this, and someone is saying the system is slow you can check for signs of slowness against these baseline figures. This can be done by having perfmon collecting the data on the database machine. An alternative, if you are happy with only seeing the last hour of performance data, you can try [Confio Ignite Free]. It would give you some idea of what is going on with your system. (I am not affiliated with Confio, just a happy user!) Saying "its slow" is like saying "it hurts", the question that has to follow is "what is slow/hurts?". You really need to explain to users/their managers that "its slow" is an unsuable statement. "It" needs to be fully qualified, e.g. "The daily orders report for yesterday ran for 10 minutes, it is normally finished in 10 seconds." This lets you know exactly where to start looking. :
+1 to @WilliamD, a baseline is vital to understand if the server is slow. The other advice to gather metrics using server side trace (don't use the Profiler gui) and performance monitor are all excellent as well. The one thing I'll add is to use the DMVs to capture wait statistics. You can start to sys.dm_os_wait_stats with other DMVs to figure out what is slowing down the system the most. That's where you can focus your efforts. By the way, my [24 Hours of PASS] session coming up in about three weeks is specifically on how to figure out which queries are running slow on the system. :
There are many good answers here, but let me add a question: Are you certain the slowness is in the SQL Server? I am assuming your users are not making direct connection with fairly low level tools like osql or even through SSMS. Could the application be the slow point? This is an especially important question if the application they are using is browser based and therefore relying on a webserver in addition to the SQL server. I have seen cases where I was asked to look for something slowing down the SQL Server and discovered that it was the web server that was causing the problem. I have also had cases where the problem was indeed tied to the SQL Server but the root cause was hard coded sql inside the application code that was not well written.