I am trying to manage some SQL server 2005/2008 boxes which have performance issues. The applications running on top of sql server are BI/BW types which run overnight reports/queries. They struggle to complete before morning 9 am deadline & I have been asked to investigate. The app servers don't do much but I can see high CPU peaks overnight thru Windows performance monitor.
I can see there is an issue and now task is to underpin where exactly the problem is. It may be related to a rouge query, wrong parameters, unusable tables/indexes, slow disks/network or something else.
I wish to know if there is repository within SQL server very similar to Oracle grid control which snapshots hourly performance related data. If Yes, how can I access it?
I wouldn't restart SQL Server. You can use DMV's as stated above. In SQL Server 2008 you have Management Data Warehouse which might be an Oracle AWR equivalent. Please note that it doesn't work with 2005
Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…