Jay's notes on SQL
[coding, debugging, and troubleshooting SQL Server 2K#]
Email Blog Author
RSS for comments
RSS for posts
Search this blog
Search all blogs
Browse by Tags
Jay's notes on SQL
Tagged Content List
Do waiting or suspended tasks tie up a worker thread?
I had a discussion the other day with someone about worker threads and their relation to tasks. I thought a quick demo might be worthwhile. When we have tasks that are waiting on a resource (whether it be a timer or a resource like a lock) we are tying up a worker thread. A worker thread is assigned...
29 Jul 2012
Need to find the Database Version? Last Log Backup? Last DBCC execution? Ummm….
Backup software and Maintenance plans usually take care of tracking backup schedules and maintenance schedules for us… but how else do we find this information inside SQL Server? The following query will get the last backup date and time from MSDB: select database_name , max ( backup_finish_date...
8 Jul 2012
Finding which queries were executing from a SQL Memory Dump – revisited
A message about the previous post on finding queries in a SQL Memory Dump file: Back in October I did a post on finding queries in SQL Server Memory Dumps. It has been a popular topic, but some have had difficulties following it. After reviewing this with a reader, I realized there was an...
18 Feb 2011
Using DBCC PAGE
DBCC PAGE is an undocumented – and unsupported – DBCC statement in SQL Server for dumping the raw contents of a single database page. It will return all the metadata stored in the page, and with the right detail level parameter – return the user data as well. Since this is not documented...
5 Feb 2011
How does SQL Server perform deletes in my table?
In this post we’ll look at how SQL Server deletes records in a table. In this example, we’ll be using a clustered index. For performance, SQL Server does not actually delete a row of data when requested by a transaction. By not incurring this cost when the transaction is executing,...
28 Jan 2011
Why do we need trace flag 3604 for DBCC statements?
For many DBCC statements, we need to enable trace flag 3604 before executing the DBCC command. This is done via: DBCC TRACEON(3604) GO But why do we need this? Some DBCC commands are designed to send the output to the log, attached debugger, or a trace listener. This statement simply...
21 Jan 2011
What is a slot array?
What is a slot array? Though it sounds like a line of gambling machines at the nearest casino, the slot array in SQL Server serves a crucial role in record management on a page. A database page (and all other page types) in SQL Server is 8KB (8192 bytes). A database page has three basic...
7 Jan 2011
How do I find what queries were executing in a SQL memory dump?
NOTE: This post has been updated in a new post due to an issue found with the steps in this post. The procedure is the same, but the steps here may only work with 32 bit dumps. Please read the post located at the address below: http://blogs.msdn.com/b/askjay/archive/2011/02/18/finding...
3 Oct 2010
How can I create a dump of SQL Server?
You can create a memory dump of the SQL Server process space in several ways. There are many external tools that can help you accomplish this such as userdump.exe, debugdiag.exe, and ADPlus.exe. In this post, I’ll cover 3 common ways to accomplish this for SQL Server: The most common way (sqldumper...
5 Feb 2010
Basic Debugging Concepts and Setup - Part 2 (Basic & Common Commands)
So if you read my previous post on Basic Debugging Concepts and Setup, you are familiar with how to open a SQL Server dump or attach to the SQL Server process (sqlservr.exe). You are also familiar with what symbols are and why we need them. If you missed it, you can read it here: http://blogs.msdn...
1 Feb 2010
Basic Debugging Concepts and Setup
In my blog, from time to time I will rely on debugging to explain or prove out a concept. In this context, I am talking about debugging the SQL Server engine itself – not debugging any TSQL code written to run within SQL Server (although I am sure we’ll get into that at some point). To follow...
29 Dec 2009
Page 1 of 1 (11 items)
© 2013 Microsoft Corporation.
Privacy & Cookies