Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Performance

Performance

Rate Topic

Display Mode

Topic Options

Author

Message

su_kumar11

Posted Wednesday, May 14, 2008 3:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 24, 2013 10:11 AM
Points: 105, Visits: 439

Hi,

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?

Thanks,
Sunil

Post #500260

GRE (Gethyn Ellis)

Posted Wednesday, May 14, 2008 3:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

You could try profiler and perf mon

Gethyn Ellis

gethynellis.com

Post #500264

josemyj

Posted Thursday, May 15, 2008 1:45 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:33 AM
Points: 15, Visits: 98

As a beginning...

First you should read this: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

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 »

Permissions