Skip to main content

HOWTO: SQL Server: Reporting on performance

Some resources to draw info on SQL Server memory usage, locks, fragmentation, etc. This is my favorite SQL Server tuning site: http://www.sql-server-performance.com/

SQL Server Perf Mon

SQLServer: Access Methods - Page Splits/sec: Number of page splits occurring as the result of index pages overflowing.
SQLServer: Databases - Data File Size (KB)
SQLServer: Databases - Log File Size (KB)
SQLServer: Databases - Log File Used Size (KB)
SQLServer: General Statistics - User Connections: Number of users connected to the system.
SQLServer: Locks - Average Wait Time (ms): The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
SQLServer: Locks - Number of Deadlocks/sec: Number of lock requests that resulted in a deadlock.
SQLServer: Memory Manager - Total Server Memory (KB): Total amount of dynamic memory the server is currently consuming
Many, many others…

Queries

View Blocks
SELECT spid, blocked, waittype, getdate() AS 'Time'
FROM master..sysprocesses (nolock)
WHERE blocked <> 0

View a whole lot of info on processes
SELECT *
FROM master..sysprocesses (nolock)

View process info sorted by memory usage
SELECT *
FROM master..sysprocesses (nolock)
ORDER BY memusage DESC

How to monitor SQL Server 2000 blocking


Stored Procedures (system)
Sp_lock
Sp_who
Sp_who2
Sp_monitor
sp_spaceused

Comments

Popular posts from this blog

TFS Error | The type initializer for 'Microsoft.TeamFoundation.Build.Server.BuildInformationNodeBinder' threw an exception.

Posting this one for the search engines. If you ever receive the exception "The type initializer for 'Microsoft.TeamFoundation.Build.Server.BuildInformationNodeBinder' threw an exception.", more than likely, your drive space is at 0 on your TFS application tier box.

I encountered this at a client recently. The root cause was that IIS logs had filled up the OS drive (C:\). I switched IIS logging to the applications drive (D:\) which cleaned up the OS drive and resolved the issue.

Detailed message:


TF53010: The following error has occurred in a Team Foundation component or extension:


Date (UTC): 6/7/2011 4:18:53 PM

Machine: TFSATBOX

Application Domain: /LM/W3SVC/8080/ROOT/tfs-1-129519118182628600

Assembly: Microsoft.TeamFoundation.Framework.Server, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727

Service Host: 7ecfbd77-b386-4d75-b038-b05474782696 (DefaultCollection)

Process Details:

Process Name: w3wp

Process Id: 3676

Thread Id: 5752

Ac…

Windows 10 Creator's Update and Office Conflicts

Recently, Microsoft pushed the "Creators Update" out to Windows 10. It was available in April but it seems to have been force-pushed in the past 2 days. On my mother-in-law's computer, Creator's caused Excel to render a "Bad Image" message when attempting to start declaring "MSVCP140.dll is either not designed to run on Windows or it contains an error". I attempted the SFC and DISM repairs as advised on Tom's site to no avail. When I attempted an Office repair, Office disappeared completely. At this point, I definitely started to panic.

Fortunately, I noticed a pending reboot due to an install/update. Allowing Windows to install that update and rebooting twice brought Excel and Office back to life. I did have to re-register the software but all is well now. If you encounter this issue, stop what you're doing and install/restart Windows. Anecdotally, I've since hear of many folks encountering similar issues.

Verified by Visa: Everything We Tell Folks to Avoid

Phishing is defined by F-Secure as: "Fraudulent e-mail or website claiming to be legitimate seeking indentifiable information. Phishing is an attempt to steal your personal data."When I recently attempted an online purchase from WalMart using my VISA card, being a security wanta-be, I immediately thought phishing when redirected to verifiedbyvisa.com and saw this dialog:

Seriously, these folks have to be kidding. You're asking for my personal data during a transaction and claim that's its a service "...at no additional cost." Wow! Thanks...but absolutely not, you jokers. As a malicious thief, I can go a long way with this data.

This is exactly the type of experience which aids malfeasance and the folks trying to steal personal data / identities. How long have we been working to educate folks to avoid providing this type of data under these type of circumstances? Years. And we're just now starting to turn the corner.

VISA, get rid of this! When folks submi…