Category: SQL Server
-
SQL Object Decryption
Background The ability to “decrypt” an object in SQL Server be it stored procedure, view, function or trigger is something that I have run into a few times. In this post I will show you how you can “decrypt” any sql object using a simple script and highlight the things to be aware of. Firstly…
-
SQL Server Memory Usage – Buffer
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…
-
Failing the MCM Lab exam
Well I failed the MCM Lab exam which I spoke about last time. The results came in 3 weeks so nice and quick. In a few ways I am happy I failed as I didn’t feel like I deserved to pass on this attempt as my answers were not up to my own standard. Plus…
-
Post MCM Lab Exam 88-971 thoughts etc
This is a quick update on my MCM progress as yesterday (5th July) I took the lab portion 88-971. I am afraid this post will not go into any content detail as it is all covered by NDA, but I thought I would post my thoughts on my exam experience. If like me you live/work in the…
-
TORN_PAGE_DETECTION Internals (SQL 2005 onwards)
CHECKSUM is in the majority of cases the more appropriate PAGE_VERIFY option as it provides a much more complete check (slightly higher overhead) when compared to TORN_PAGE_DETECTION. I thought today however I would write a simple post to show how the internals of TORN_PAGE_DETECTION work even tho it is less used. I am guessing that you have…
-
SQL Server Max Row Size 8060 Part 2
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…
-
Passed MCM Knowledge Exam 88-970
Well back in January I mentioned my plan to study towards the MCM certification. Well… I passed 88-970… for those who want to know more read on. I said I planned to take the exam in the summer maybe, and see how I went from there. However after reading one of Brent Ozar’s articles on…
-
Page Life Expectancy
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
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
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…