Tuesday, May 21, 2013

[SQL Server] SQL 2008 R2 performance monitoring and troubleshooting

[SQL Server] SQL 2008 R2 performance monitoring and troubleshooting

SQL 2008 R2 performance monitoring and troubleshooting

Posted: 21 May 2013 12:33 AM PDT

Hi folks,I'm a Network Admin that has been assigned the task of evaluating our SQL server for performance issues...and when I say assigned I mean my manager came to me and said "here its your problem now...fix it".[b]Background:[/b]We are a Microsoft shop, the system in question runs on windows 2008 R2, 4 CPUs, 16GB of memory and its visualized with Microsoft HyperV 3.0 (Windows 2012), SQL 2008 R2 using windows authentication.The host server this virtual server sits on is a Quad CPU 64 core (total), 256GB of RAM with 14 NIC ports (2 10GBit), the virtual disk sit on a DELL Equalogic iSCSI based storage system. [b]The problem:[/b]The primary program that utilizes the SQL server in question is Financial Edge, created by Blackbaud. This program is accessed through Citrix (Terminal Services).Users have been complaining extensively about slow query processing times, and even claiming to have queries take 6-8 hours (cant verify this as they never call IT when it happens). Our department has TRIED the obvious things have them recreate the query, which does work in some cases. unfortunately management is now saying there end users have no time to troubleshoot....:crazy: ....and they think the problem is with our systems and not a end user issue...typical[b]What to do:[/b]Unfortunately I know very little on SQL in terms of performance tweaking, as I mainly focus on Visualization. So I did a little digging around and found [url=pal.codeplex.com]PAL[/url], it has performance counters i can use with SQL 2k8 R2. In my first phase of testing I ran it 4 times a day at 30 intervals for 1 hour. Doing this i was able to narrow down peak times when the SQL server gets hit the most as well as possible DISK I/O issues, unfortunately this is the most Ive been able to "decode" from the performance report. Among the alerts where:◦SQLServer:Access Methods FreeSpace Scans/sec (Alerts: 2)◦SQLServer:Access Methods Page Splits/sec (Alerts: 1)◦SQLServer:Access Methods Index Searches/sec (Alerts: 2)◦SQLServer:Access Methods Worktables From Cache Ratio (Alerts: 30)◦SQLServer:Buffer Manager Page life expectancy (Alerts: 30)◦SQLServer:Buffer Manager Page lookups/sec (Alerts: 7)◦SQLServer:Buffer Node Page life expectancy (Alerts: 30)◦SQLServer:Databases Log Flush Wait Time (Alerts: 91)◦SQLServer:Databases Log Flush Waits/sec (Alerts: 2)◦SQLServer:Databases Log Growths (Alerts: 60)◦SQLServer:Deprecated Features Usage (Alerts: 960)◦SQLServer:Latches Latch Waits/sec (Alerts: 30)◦SQLServer:Locks Lock Requests/sec (Alerts: 5)Not sure if this alone is useful or i need to attach the entire report (which I will in a few)

Database Diagram

Posted: 20 May 2013 06:39 PM PDT

Hi Guys.After creating a new database diagram,i don't see any relationships.It's only showing the tables.I tried selecting the show relationships but nothing comes up.How do i get it to show the relationships?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog