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

Rollback a Ooops in TFS with TFPT Rollback

Rhut roe, Raggie. You just checked in a merge operation affecting 100's of files in TFS against the wrong branch. Ooops. Well, you can simply roll it back, right? Select the folder in Source Control Explorer and...hey, where's the Rollback? Rollback isn't supported in TFS natively. However, it is supported within the Power Tools leveraging the command-line TFPT.exe utility. It's fairly straightforward to revert back to a previous version--with one caveot. First, download and install the Team Foundation Power Tools 2008 on your workstation. Before proceeding, let's create a workspace dedicated to the rollback. To "true up" the workspace, the rollback operation will peform a Get Latest for every file in your current workspace. This can consume hours (and many GB) with a broad workspace mapping. To work around this, I create a temporary workspace targeted at just the area of source I need to roll back. So let's drill down on our scenario... I'm worki...

TFS Reports Out of Date

You may have noticed it takes a while for Team Foundation Server (TFS) reports to reflect changes you've made to work items or builds. Let me guess...about an hour, right? Out of the box, TFS is set to refresh the data warehouse from its transactional store every 60 minutes. How do I change the frequency of the data warehouse refresh? Browse to the TFS Controller Web Service on your TFS application tier server within IE at: http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx Select the ChangeSetting option Enter RunIntervalSeconds for the settingId and the desired number of seconds for newValue (300 for 5 minutes...5*60) Select Invoke How do I force a data warehouse refresh? Two methods here: either via the above web service or using SQL Server Management Studio. Via the web service: Browse to the TFS Controller Web Service within IE at: http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx Select the Run option Click Invo...

Certified or Certifiable?

As a senior technology professional, I interview a lot of candidates. I also maintain solid relationships with other folks in the community. Frequently, the topic of certifications arises: A good investment? Valuable? A clear measurement of skill? Consensus appears to draw the line related to one's seniority. If you're (for example) just out of school and looking for an instant creditability boost, by all means pursue a certification. Likely, this credential will assist you in overcoming the "junior" tag and likely land you more interviews and client roles. (Note: I'm going to use the terms senior and junior here...no offense to either. Can't think of a better one word description. I was a junior once too.) In stark contrast, the value of certifications drops off the table around the 2-3 year mark. Some in my circles even perceive certifications as a negative for the senior professional. They think, "If this guy is so solid, why is he wasting valuable...