Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds two MCITP certifications in SQL Server 2008. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.

How to Tell If Your Users are Connecting to the Availability Group Listener

You've spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance?

So why would we care about this? …

Read more

0 comments, 1,202 reads

Posted in Everyday SQL on 27 August 2013

PASS Summit 2013 - You Ain't From Around Here Are Ya?

I know what y'all are thinkin', what's Charlotte got to do with SQL Server?  Just hear me out.  There's a lot more to Charlotte than NASCAR, fried chicken, and rednecks. I assume most of the 5000 attendees have never been to Charlotte, and probably don't know much about the area.  To…

Read more

0 comments, 418 reads

Posted in Everyday SQL on 20 August 2013

Are You the Primary Replica?

In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica.  The gap being Microsoft did not provide a DMO to return this information.  The good news is the documentation for the…

Read more

0 comments, 558 reads

Posted in Everyday SQL on 23 July 2013

Setup an Availability Group with Multiple Subnets in VMware Workstation


Before we get started, I want to make it clear this is NOT how you would normally configure all these items in a production environment.  This is meant for a lab or demo area to play with Availability Groups over multiple subnets.

I use VMware a lot for demos at…

Read more

0 comments, 810 reads

Posted in Everyday SQL on 16 July 2013

VMware vSphere Storage Performance - Thick vs Thin Provisioning

Industry experts will tell you that virtualization of your environment is not done to improve performance, it's done to make it cheaper and easier to manage.  The task of most VM administrators is to cram as many VMs into a farm as possible.  One of the ways is to accomplish…

Read more

0 comments, 1,337 reads

Posted in Everyday SQL on 25 June 2013

Investigating Plan Cache Bloat

SQL Server includes a DMV, sys.dm_exec_query_stats, that returns performance statistics for each query plan cached in memory.  However, it can also help give you insight into how consistent your developers are with writing code.

For this topic, we'll just concentrate on a few columns returned by the DMV: sql_handle

Read more

0 comments, 517 reads

Posted in Everyday SQL on 28 May 2013

An Alternative to SELECT COUNT(*) for Better Performance

Sometimes rapid code development doesn't always produce the most efficient code.  Take the age old line of code SELECT COUNT(*) FROM MyTable.  Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a…

Read more

0 comments, 1,610 reads

Posted in Everyday SQL on 7 May 2013

Merging SQL Server and Softball Just for Fun

With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball.  Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have.  I…

Read more

0 comments, 607 reads

Posted in Everyday SQL on 2 April 2013

Use Powershell to Pick Up what Database Mirroring Leaves Behind

Database mirroring has been around since SQL Server 2005, and it's turned out to be an excellent step up from log shipping.  However, like log shipping, it is still only a database-level disaster recovery solution.  Meaning that any logins, server role memberships or server-level permissions will not be mirrored over…

Read more

0 comments, 1,238 reads

Posted in Everyday SQL on 26 March 2013

T-SQL Tuesday #40 - Proportional Fill within a Filegroup


T-SQL Tuesday #40 is underway, and this month's host is Jennifer McCown (blog|twitter).  The topic is about File and Filegroup Wisdom.  Jennifer says she's a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup.  This…

Read more

0 comments, 1,524 reads

Posted in Everyday SQL on 12 March 2013

Dealing with a Fragmented Heap

Just for the record, this happens to be one of my favorite interview questions to ask candidates.

At some point in time, there will be a database containing tables without clustered indexes (a heap) that you will be responsible for maintaining.  I personally believe that every table should have a…

Read more

0 comments, 1,248 reads

Posted in Everyday SQL on 5 March 2013

The Performance Impact to Prefix Stored Procedures with sp_

Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries.   In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple…

Read more

0 comments, 576 reads

Posted in Everyday SQL on 19 February 2013

T-SQL Tuesday - Use Powershell to Restore a Database on a Different Server

T-SQL Tuesday - This month's party is hosted by Wayne Sheffield (blog|twitter), and the topic is about Powershell and how to use it for anything SQL Server.

With that challenge, I'd like to share a script I've written that takes a backup file from one server, copies…

Read more

3 comments, 4,104 reads

Posted in Everyday SQL on 12 February 2013

What is DBCC SHOW_STATISTICS Telling Me About My Data?

Per Books Online, DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view.  Basically it shows you the statistics, or a summary of the data, that SQL Server will use to help generate an execution plan.

In the example below, we'll be looking at the statistics for…

Read more

1 comments, 3,077 reads

Posted in Everyday SQL on 5 February 2013

How to Remove (Undo) Table Partitioning

I have seen plenty of articles and blog posts out there for how to setup and implement table partitioning, but very few for removing or undoing it.  So I thought I would cover a few ways to accomplish this while still preserving the data.

There could be many reasons for…

Read more

3 comments, 2,131 reads

Posted in Everyday SQL on 29 January 2013

sp_PerformanceCounters - Get a Health Check of SQL Server's Performance

During your career as a DBA, you'll run across articles by SQL experts or other DBAs that just give you an idea.  A few years ago I found an article, or a poster to be more precise, that explains SQL Server performance counters.  It was written by Kevin Kline, Brent…

Read more

3 comments, 645 reads

Posted in Everyday SQL on 22 January 2013

Exam 70-432 - SQL Server 2008 Implementation and Maintenance

For years, actually more like a decade, I've been saying that I'm going to get certified in SQL Server.  Well finally after years of saying it, I finally did.  I passed my first exam, 70-432, this past week.  I have to say it was easier and at the same time…

Read more

0 comments, 467 reads

Posted in Everyday SQL on 15 January 2013

SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations

Have you seen this type of an informational message in your SQL errorlog before?  If so, then I hope the timestamp associated with it was not during the middle of your peak processing time.  This is a good indication that someone has been tinkering with some of the SQL configuration…

Read more

0 comments, 369 reads

Posted in Everyday SQL on 7 January 2013

How to Use xp_dirtree to List All Files in a Folder - Part 2

In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder.  The previous posting mostly talked about how the stored procedure worked and the output it generated.  For this posting, I have expanded upon the script…

Read more

0 comments, 3,180 reads

Posted in Everyday SQL on 31 December 2012

SQL Saturday #143 - Washington, DC

Last week, I attended my very first SQL Saturday event in Washington, DC.  Although I don't live anywhere near Washington, I was able to make the short trek from Charlotte, NC.  It was well worth the time and effort.  I missed the previous one in Charlotte back in October, but …

Read more

0 comments, 245 reads

Posted in Everyday SQL on 18 December 2012

Newer posts

Older posts