Post reply

Performance

  • May 14, 2008 at 3:22 am

    #186629

    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

  • May 14, 2008 at 3:25 am

    #815009

    You could try profiler and perf mon

    Gethyn Elliswww.gethynellis.com

  • May 15, 2008 at 1:45 am

    #815670

    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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply