Most of the time I work with SQL Server 2005 when I need to collect statistics and create a nice looking html document with #PerfMon stats. In that case I run SQLDIAG with detailed statistics enabled. Command at the prompt is: SQLDIAG /i SD_Detailed.XML. This time around I was asked to run a performance report on a SQL 2000 Server. It's been a long time since I've done any work of this type so here are a few things I've learned from working with it. Apparently SQL 2000 doesn't support SQLDIAG. In that case I need to run PSSDIAG. You may ask why do I need to run either one, there are other tools available. Well, SQLDIAG and PSSDIAG create a .BLG file that I can feed into PAL tool that will create a really good html formatted document with plenty of information on performance. Well, my problems started right away. It is not as flexible as SQLDIAG and I stopped taking 2005 for granted after I've messed around with PSSDIAG for 2000. Apparently I need XML parser SP2 to run PSSDIAG on SQL 2000. Running it without that will give you an error message "cannot instantiate XML DOM". Oh ye, double-clicking the PSSDIAG executable will give you a nice "Buffer Overrun" error message. How about that! If anyone has encountered similar issues, please let me know what are your thoughts. If you're wondering whether I was able to collect the data, the answer is no. I need to wait seven days as per our change control policy for any modifications to our production environments. So even a 5mb file (XML parser Sp2) has to be installed during a scheduled maintenance.
Take care and have a great weekend!