A while back, I spent about a month working through several SQL Server performance issues after upgrading three systems from SQL Server 2000 to SQL Server 2005. During that time I would realize that my knowledge of how SQL actually manages data at a low level was sketchy. In the process of resolving said sketchiness, I came across a few resources that proved helpful. [Read more…] about Resources for Learning More About SQL Server Internals
SQL Admin
Always Have Installation Media Ready During Maintenance
Make sure when you are doing maintenance work on SQL Server (or any application) that you have the installation files handy. Although you might never need them, you’ll be happy the one time maintenance goes bad and you need to rebuild something.
Working With the Master Database
I have a SQL Server 2000 I need to upgrade to SQL Server 2005. In considering different ways of doing this (side-by-size, overlay, move to a new machine entirely) I set up a SQL 2000 instance on a DEV server and started playing around with the master database. Here are a couple of things I encountered that are worth sharing. [Read more…] about Working With the Master Database
Get SQL Command Executing by SPID
I’m sure there are other ways to do this but this is pretty easy:
DECLARE
@SPID INT,
@Handle binary(20),
@retVal varchar(8000)
SET
@SPID = 148
SELECT
@Handle=sql_handle
FROM
master..sysprocesses
WHERE spid = @SPID
SELECT
SqlCommand=convert(varchar(8000), [text])
FROM
::fn_get_sql(@Handle)
References
Partitioning and Formatting Disks for SQL Server Performance
When you add new disks to a server and you intend to use them for SQL Server log and data files, there are some suggested best practices in preparing the disks. I suggest you read this post, then read the resources below before taking any action. Some of the configuration values in the Partitioning and Formatting steps below are not universally agreed upon despite strong evidence to support their validity. It is important that you understand the nature of your environment and come to your own conclusions regarding configuration before you proceed. [Read more…] about Partitioning and Formatting Disks for SQL Server Performance