November 23, 2009 at 9:47 am
My Dear Experts,
Windows 2000 server,IBM,12 gb ram with 2005 Sql server, our database is morthan 200gb size and endusers are >200
now our clients compliant appliactions are very slow while thay are saving the entries , So here I have used some strategic such as
Shrink db, Query Optimizations, re indexing,deleting unwanted index, but still it's giving same issue,
Can u help me to overcome this Problem, is there any way to like sp_configure or increasing the sql server memory ,performace else....
I need clear steps i am in very critical situations..
Thanks in advance..
November 23, 2009 at 10:12 am
First try to analyze the problem and then take the action accordingly. Try to run the profiler and check whats happenning. Check CPU and Memory utilizations. Try to provide more information. Thanks!
November 23, 2009 at 10:13 am
I would suggest performing some diagnostic steps to determine why the system is performing slowly so you know where to focus your efforts.
If this were me, I'd set up a Windows Performance Monitor session as well as a SQL Profiler trace. Use the perfmon session to capture server performance metrics. For a high level, first-look at a server, I like to look at the following counters:
Processor>% Processor Time>_Total
PhysicalDisk>Current Disk Queue Length>_Total
Memory>Available Mbytes
SQLServer:Access Methods>Page Splits/sec
SQLServer:SQL Statistics>Batch Requests/sec
SQLServer:Databases>Log Flushes/sec
SQLServer:Buffer Manager>Buffer cache hit ratio
SQLServer:Buffer Manager>Page life expectancy
SQLServer:Buffer Manager>Lazy writes/sec
SQLServer:Buffer Manager>Checkpoint pages/sec
SQLServer:Memory Manager>Total Server Memory (KB)
SQLServer:Memory Manager>Target Server Memory (KB)
Process>Page Faults/sec
For the Profile trace, I like to start with these counters:
Database:: Data File Auto Grow
Database:: Data File Auto Shrink
Database:: Log File Auto Grow
Database:: Log File Auto Shrink
Errors and Warnings:: ALL
Locks:: Deadlock
Locks:: Timeout
Performance:: Showplan Text (unencoded)
StoredProcedures::RPC Started / RPC Completed
StoredProcedures::SP Started / SP Completed
StoredProcedures::SP StmtStarted / SP StmtCompleted
TSQL:: StmtStarted / StmtCompleted
These 2 monitor sessions should give you a basic idea of where to focus. From the Perfmon, you'll learn about your server's resources, how it is handling the load from SQL Server, and if there are areas that you want to pay attention to. From the SQL trace, you'll get information on query durations, reads, cpu and other database activity like errors/warnings, file management, deadlocks. This will give you the ability to seek out potentially poor performing queries and fix them. Run the 2 sessions at the same time for 1-2 hours so that you can correlate server resource spikes with poorly performing queries or other database activities.
Performance tuning a SQL Server system is more of an art than a science. You need to become familiar with the basic tools for troubleshooting and learn how to use them effectively. Sometimes it's like looking for a needle in a stack of needles, but if you can get good at using these tools to isolate problems, you'll be worth your weight in gold.
November 23, 2009 at 10:14 am
Saravanan_tvr (11/23/2009)
My Dear Experts,now our clients compliant appliactions are very slow while thay are saving the entries ,
It would seem that you would get the biggest bang for your effort by tuning the queries, indexes, keys, etc involved in the same process.
You say you have already optimized queries and indexes. They could probably still be optimized more. To better assist, we would need to see table structures, execution plans, and queries.
Other than that, you need to know for certain where your bottleneck is happening. Is it truly sql server, or could it be the disks? What kind of disk setup are you using?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply