I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.
The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.
So here’s my general ramblings and thoughts about working as a SQL Server DBA.
You can find me on twitter @DBAFromTheCold
If you have any feedback on my blog please send me an email to email@example.com.
This post follows on from In-Memory OLTP: Part 2 – Indexes
So far in this blog series memory optimised tables have been created with the durability option of SCHEMA_ONLY meaning that data will not be retained. However there is another durability option of SCHEMA_AND_DATA which means that SQL will retain… Read more
Following on from In-Memory OLTP: Part 1 – Introduction where we created a database capable of hosting memory optimised tables, I’d now like to run through the indexing options available.
As I mentioned in the last post, memory optimised tables do not have data pages. They are data rows written… Read more
In-Memory OLTP is getting a lot of (rightly deserved imho) hype at the moment. But what does it entail exactly? If you’re a DBA with a few years experience under your belt and are looking to get into this new feature but don’t have the time to sit and read… Read more
Back in October 2014 Midnight SQL released v1.0 of Minion Reindex, a free, open source index maintenance solution. I’m all for making my day job easier so I once I heard about this I wanted to get it into a development environment asap to see what it can do. Unfortunately… Read more
There are a number of ways that you can identify blocking that is occurring in your SQL instance. You can run the undocumented sp_who2 stored procedure but that will only give you the session that is being blocked. You could download the excellent sp_whoisactive, written by Adam Machanic, which… Read more
I have recently seen some “bad plans” being generated by the optimiser and from investigation, the cause came down to the fact that the collation of the database where the queries were running was different to the tempdb collation.
Consider this situation, you have a stored procedure which collects various… Read more
I know it’s late but I’ve been away, I hope you all had a good Christmas and New Year.
I try and regularly review my skills as a DBA but more so at the start of a new year. You know the type of questions:-
- Have I improved my overall…
The new SQL Server 2014 feature In-Memory OLTP (code-named “Hekaton”) has been attracting a lot of interest since its release, promising to deliver (if you believe the rhetoric) an increase of up to 100 times in performance.
If you’re like me, you’ve got a copy of SQL Server 2014 Developer… Read more
A couple of weeks ago I was contacted by Webucator (an online training company) asking if they could use my blog post “Identifying Large Queries using Extended Events” for a video training session in their free series called SQL Server Solutions from the Web.
The main reason… Read more
I’m actually quite proud of the fact that I’ve now been doing this for a year now (and that I’m still going).
So one year on, what have I learnt?
Get your blog syndicated with other, larger websites. The amount of views my blog got went through the roof… Read more
Each time you view a seek/scan operator in an execution plan, you may have noticed that there’s a value for the estimated number of rows and a value for the actual number of rows. Sometimes these values can be fairly similar and sometimes they can be very different.
The estimated… Read more
Following on from my last blog post I now want to run through how to identify large queries using Extended Events.
Extended Events were introduced in SQL Server 2008 and allow monitoring to be run against a server with a lower performance impact than profiler sessions and server side traces. Read more
Who are your worst offenders? By offenders I mean, queries that are consuming the most resources on your server(s).
I know Extended Events have a lower impact but I like server side traces. Not only because once you’ve set one up, setting up others is very simple but also because… Read more
Performance tuning often gets called an art as people feel that a certain knack or innate talent comes into play. And whilst I don’t disagree that a certain level of knowledge is involved, I completely disagree that only certain people can performance tune.
Given the correct approach, anyone should be… Read more
Short blog post this time as Website Pulse contacted me a few weeks ago with a few questions about working as a DBA. They’ve now published the article, it can be found at the following link:-
Let me know if you have any comments or feedback – firstname.lastname@example.org
This post follows on from Partitioning Basics – Part 2
In this final part, I want to go through how partitions can be used to archive data from a table. Firstly a table to archive the data from the primary table needs to be created:-
CREATE TABLE dbo.[DemoPartitionedTable_Archive] (DemoID INT…
This post follows on from the previous post Partitioning Basics – Part 1
Let’s have a look at the partitions setup in part 1, the following script will show the partition information:-
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, r.value AS BoundaryValue, p.rows FROM sys.tables AS t INNER JOIN…
Partitioned tables can be a quick and efficient way to (amongst other things) archive data. In the next couple of posts I will run through the basics of partitioning. Starting with setting up a partitioned table and loading some data.
Let’s create a demo database:-
USE [master]; GO IF EXISTS(SELECT…
Thought I’d write a quick post about managing error logs within SQL Server. Good error log management is essential for investigating issues that occur in SQL Server. The last thing you want is a very large error log file that takes ages to open when you are trying to determine… Read more
One of the developers that I work with asked me to write a “brief” (really brief) guide on database design. What I have written below is a summary in bullet point form of best practices when creating databases, tables, indexes, and then querying the data within. It is designed to… Read more