Tag: Script

What is the SQL Server Buffer Pool? I am purposely keeping this post short because I simply want to offer up my script to show the current amount buffered. My version is very similar to that found on the main sys.dm_os_buffer_descriptors page, but I prefer MiB rather than pages. This next script will break down the buffer…

SQL Server Max Row Size 8060 Part 2

Jun 9, 2012

—

by

In my previous post on row maximum length I showed that the largest row size that could be stored on page was 8053 byte. So to recap a table with 2 fixed length columns CHAR(8000) & CHAR(53) the internal storage would look as below, with an unused space of 34 bytes. Page Header 96 bytes…

Page Life Expectancy

Apr 20, 2012

—

by

Page Life Expectancy Page Life Expectancy is often quoted as being a key metric to watch out for memory pressure within SQL Server. If you search you will often see 300 listed as a low water mark to indicate that you may have a problem, indeed several vendor applications raise warnings at this threshold. The standard definition…

SQL Servers Virtual Columns and Row Cracking

Apr 16, 2012

—

by

In order to access row structures there are several functions within SQL Server that are beneficial. I have listed these below with the descriptions taken from the actual functions. sys.fn_GetRowsetIdFromrowdump — Cracks the output of %%rowdump%% virtual column and returns the rowset id sys.fn_rowdumpCracker — Cracks the output of %%rowdump%% virtual column sys.fn_physlocFormatter — Formats the…

SQL Server Max Row Size 8060

Apr 13, 2012

—

by

When asked the size of the largest row that could be placed on to a single SQL Server data page the answer that most give is 8060 bytes. If you try to create a fixed length table with columns that total this length however it throws an error. Please create a database to run this test and…

SQL Server Instance Security: Scripting Permissions Part 2

—

by

After writing a powershell way of scripting permissions I thought I better just show the way I used to do it using T-SQL. Although this script is not complete and is kind of relic of my scripting work I thought that I would include it and perhaps maybe if it is on this blog it…

SQL Server Instance Security: Scripting Permissions

—

by

in Powershell

Another How-To post and this time focusing on scripting permissions. Here is how to script User(s), Object(s), Role(s), Server Role(s) and Server Object(s) Permissions. Beginning with the simple way via SSMS, before showing a custom script solution that I have written. Scripting SQL Server Security: Simple Solution In order to script the security settings for…

Capturing Deadlocks

—

by

So, deadlocks. “A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.” http://msdn.microsoft.com/en-us/library/ms178104.aspx How to capture deadlocks? How many ways can you capture a deadlock? Lots! Luckily I do not see a lot of deadlocks in…

SQL Server Profiler Trace

—

by