January 18, 2012 at 7:14 am
i have a database of 100 GB
recently we are facing some problem to performance.
the front end application is running slow.
what is the basic task to do in DB
Please Help
January 18, 2012 at 7:47 am
Or hire a consultant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 8:07 am
i m a consultant 😀
January 18, 2012 at 8:07 am
Dhirju (1/18/2012)
i m a consultant 😀
I'll be more specific then. Hire a consultant that specialises in performance tuning.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 8:10 am
you could start by looking at your 10 worst performing queries;
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
January 18, 2012 at 8:11 am
i agreed. but as a DBA we should know the problem.
January 18, 2012 at 8:15 am
Save yourself some time - just hire Gail.
January 18, 2012 at 8:17 am
Geoff A (1/18/2012)
you could start by looking at your 10 worst performing queries;SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
Just bear in mind that won't include queries marked WITH RECOMPILE, OPTION(RECOMPILE) and it may not show queries whose plans are frequently evicted from cache and recreated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 8:20 am
Agreed Gail, but without firing up SQL Profiler, it is sometimes a good start.
seems the poster just needs a push to start looking somewhere.
January 18, 2012 at 8:46 am
I know that you might have set up these tasks but I have seen a zillion times that the most basic database maintenance tasks are not set up properly.
Take a look at index fragmentation and update the stats. If these two tasks are not configured properly then no amount of query tuning will help you.
Check when was the last time the stats got updated, please do not rely on the automatic stats to take care of that. I am sure with a DB of just 100 GB you should be able to run a full scan update stats in a reasonable amount of time.
Blog
http://saveadba.blogspot.com/
January 18, 2012 at 8:52 am
I would set up an extended events session to capture the queries being executed on the server. I would also start monitoring the server using Performance Monitor.
You need to establish if the server is the problem first. Tune the hardware. Tune the operating system. Tune SQL Server. Then, determine if you have an issue in the code or the database structure. It's usually the code, but it can also be the database. Tune the database. Tune the code. These last two you have to do over & over agian.
How to do all that? Books have been written on the topic (look down at my signature for one example).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 18, 2012 at 9:08 am
Stat and index tuning are good starting points.
Wait states can tell you about hardware bottlenecks, if there are any.
Confio Ignite or one of its competitors can be a great way to narrow the target on what to tune, code-wise. Even the free version has enough data in it to make tuning much easier to start. Won't tell you how to fix, but will tell you what to fix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 9:22 am
Run a SQL Profiler for about a half hour to hour on just that database id, and sort by duration descending and save the trace file (.trc). That will give you a real good clue on the queries/stored procedures that are taking the most time to execute, then pull the execution plans on those queries. Of course, I am assuming here you know how to read an execution plan. If you are a db consultant, you should. Then I would run the Index Tuning wizard on that database using the .trc file above as well and see what recommendations it points out. That's just for starters. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 9:48 am
TravisDBA (1/18/2012)
Run a SQL Profiler for about a half hour to hour on just that database id, and sort by duration descending and save the trace file (.trc). That will give you a real good clue on the queries/stored procedures that are taking the most time to execute, then pull the execution plans on those queries. Of course, I am assuming here you know how to read an execution plan. If you are a db consultant, you should. Then I would run the Index Tuning wizard on that database using the .trc file above as well and see what recommendations it points out. That's just for starters. 😀
The problem with that approach is that you really need to account for "fast but frequent" queries as well as long-running queries.
If you have a proc that runs in 10 seconds (very long on most OLTP systems), but only gets run once per day, that's a lower priority target than the proc that runs in 10 milliseconds, should run in 1 millisecond, and gets run 200 times per second. Sorting by descending duration will give you the 10-second proc. So also make sure to aggregate total run-time, not just look at per-incident runtime.
Also, you can have hidden performance eating code that you won't catch in a simple trace. For example, you might not notice that all your insert/update/delete operations are taking longer than they should, because of a trigger on a heavily-hit table. A trace won't usually catch that, but something like Confio Ignite, SQL Foglight, et al, will. That's why I would start with the free verion of one of those, instead of relying on a trace. They'll also catch common UDFs (another form of hidden code), that are eating up wait-state-time across multiple procs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 10:00 am
The server configuration is windows server 2003 with sql server 2005 32 bit
and RAM 12 GB.
AWE is enabled and sql is using near 10 GB of RAM resoures.
i thing first i have to check out the indexes.
And Thanks for the reply
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply