Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Performance Expand / Collapse
Posted Wednesday, May 14, 2008 3:22 AM


Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:44 AM
Points: 105, Visits: 447

Can any one tell me how can i see the performance of database and application in sql server 2005 if 1000 users are working with database?

Post #500260
Posted Wednesday, May 14, 2008 3:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 1,039, Visits: 2,823
You could try profiler and perf mon

Gethyn Ellis
Post #500264
Posted Thursday, May 15, 2008 1:45 AM


Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:56 AM
Points: 15, Visits: 116
As a beginning...

First you should read this:

To be sure you do not have any bottle neck in your system.

Then i'll start with SQL profiler, for example, with querys than lasts more than 10 seconds (depends on your server)

On perfmon, ill keep track of these counters:

Network Interface(*)\Output Queue Length
PhysicalDisk(*)\% Disk Time
PhysicalDisk(*)\Avg. Disk Queue Length
PhysicalDisk(*)\Avg. Disk sec/Read
PhysicalDisk(*)\Avg. Disk sec/Write
Processor(_Total)\% Processor Time
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Checkpoint pages/sec
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Cursor Manager by Type(_Total)\Cursor Requests/sec
SQLServer:SQL Statistics\Batch Requests/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec

Then keep track of current index usage with the dmv dm_db_index_usage_stats and prepare your own indexes taking a orientative look on dm_db_missing_index_group_stats.

Take your time as SQL Server Index Tuning Advisor checks each query separately for the whole workload, and usually recommends indexes you should mix to get one useful for many querys.

Good luck.
Post #501066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse