• SSC Rookie:

    "1- Which Counters Do I control in performance monitor in first step?"

    I use the Performance Monitor Data Collector Sets with a wide range of actual counters. I log these to a SQL table and then have custom stored procedures that pivot the data to what i need to focus on in that instance.

    (everything from General Statistics to Wait Statistics to Network/CPU/Mem...lots)

    "2- how to Use sql profiler for this check performance ?"

    I have custom trace files that execute inside a SQL Job. These kick off every 15 minutes and collect data until x sized trace file if filled. Then dump that to a SQL table for analysis & alerts trigger if thresholds passed from prior data.

    "3- what is spid performance ?"

    I received a stored procedure from a Microsoft Engineer on a case i worked with them a couple years ago. It returns active SPID's related data. I log that to a SQL table and have alerts trigger off that also. Gives a real-time view into any issues.