Blog

I’m curious. I like to know how things work. I have to read the user manual of everything I buy so I know all of its features. I like clicking all the buttons in applications I work with. However, SQL Server Management Studio (SSMS) has so many I haven’t gotten through all of them. At SQL Saturday #118 Wisconsin earlier this year, I was watching a presentation and the presenter clicked “Include Client Statistics”.

“What is this magic?!” I thought.

MSDN says it “Displays information about the query execution grouped into categories. When Include Client Statistics is selected from the Query menu, a Client Statistics window is displayed upon query execution. Statistics from successive query executions are listed along with the average values. Select Reset Client Statistics from the Query menu to reset the average.”

I mentioned it in last week’s webcast, “SSMS: More Than Meets The Eye“, but I wanted to give you more detail about it here.

This button is located on the SQL Editor toolbar.

Open a query editor window, click on it to highlight it, write a query, and execute it. Your results will look like Figure 1.

Some really valuable information can be found here, such as the number of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful that can be when tuning a complex stored procedure. You can see the number of rows affected or returned, and the number of transactions executed.

The network statistics lets you see how much traffic is moving from your client to the server and back.

The time statistics tells you how much time was spent processing on the client versus how much time was spent waiting for the server. These figures are in milliseconds.

That’s useful when you run a query once, but its usefulness becomes more apparent when you are tuning a query. Statistics will be shown for the ten most recent executions, and they will be averaged, as shown in Figure 2. Thus, as you are making changes to a query – perhaps changing the conditions in the WHERE clause, or comparing an AVERAGE to a window function – the statistics will update. You can, at a glance, compare the changes. You’ll even see green arrows for improved performance, and red arrows for worse performance.

Client Statistics is not as in-depth of a tool as, say, execution plans, showing you where the performance of your query can be improved. It does, however, give you access to information that you might otherwise need to keep track of in your head, or scribbled on a Post-It Note – like I used to do when writing report queries. It’s another useful tool in the SSMS toolbox!

At PASS Summit last week, I presented “The What, Why, And How of Filegroups” to a packed room. It was great to see so many people eager to learn about something that is fundamental to every SQL Server database, but often not understood well enough.

As always, during and after the session, there were a lot of questions. One really piqued my interest: “When creating a table or index, is there a policy in Policy-Based Management to force a query to specify the filegroup instead of using the default?”

Let’s find out!

I’m going to use my DBA database on my development instance, DBAInfo. The database currently has one filegroup, PRIMARY.

Next, I’ll add another filegroup and file, and make it the default.

I double-check my filegroups.

At this point, any table added without an “ON FilegroupName” clause should be created on the default. Let’s check.

I run my “What filegroup is that on?” query:

It has been created on the default, PBMTest, as expected.

Now, I’ll add a third, non-default filegroup, PBMTest2.

I’ll check my filegroups again.

Next, I want to create a policy that says “When a new table is created, put it on PBMTest2, even if that’s not the default.” In SSMS, I expand Management > Policy Management and right-click Policies > New Policy.

I give the policy a name, “CreateTableOnFilegroup”, and then click next to “CheckCondition” to set a condition. I name it “OnFilegroup”. The Facet is Table. I set the Field to @Filegroup, the Operator to “=”, and the Value to ‘PBMTest2’.

It’s set to every table in every database. I only want it to apply to my DBAInfo database, so I select Every > New Condition.

I give the Name DatabaseDBAInfo. I set the Facet to Database, the Field to @Name, the Operator to =, and the Value to ‘DBAInfo’. When I click OK, I’m back at the Create New Policy screen. I want to set Evaluation Mode to On demand and Server restriction to None. I click OK.

The one policy and two conditions are created.

Now, I go back to the query window and create another table, without specifying a filegroup to place it on.

What I want this to be created on PBMTest2. What filegroup is it on?

It’s created on PBMTest.

What? Why?

This comes down to the Evaluation Mode setting of my policy. There are a total of four modes available in PBM – On Demand, On Schedule, On Change – Log only, and On Change – Prevent. What we are looking to do here is enforce On Change – Prevent – if the table isn’t going to be created on the filegroup we specify, we want it to fail. Unfortunately, not all policies can use this mode.

I had to do some research to figure this out. First, read this blog post on Facets by the PBM team. At the bottom, there is a table showing the facets and what modes are supported. Then, read this further explanation of the types by Lara Rubbelke, Policy Evaluation on a Single SQL Server 2008 Instance. Lastly, there is this blog, Policy Evaluation Modes,by the PBM team.

For On Change – Log and On Change – Prevent to exist for a facet, there must be a DDL event triggered by it. This is not the case with every operation in SQL Server. The CREATE TABLE commands in one such example. Because of this, I cannot create a policy to check what filegroup a table is being created on before it is created.

In this case, I can still create the policy and check it either on a schedule or on demand. It will then tell me which tables were not created on that filegroup.

Thank you Simon for the great question!

If you became a database administrator by rising through the ranks of software developers, you probably haven’t built your own servers from the ground up. That’s totally okay – but you’re missing some important information that will make you a better database administrator. In this 20-minute session, Brent Ozar will help you get started exploring your hardware using tools like HP System Insight Manager, Dell OpenManage, and IBM Director. He’ll explain what you’re looking at – and when you should raise an alarm because something’s not quite right.

https://www.youtube.com/watch?v=N2gVp31uojw

I have been in love with books and libraries most of my life.

I remember some of my earliest favorites: Little House on the Prairie, Anne of Green Gables, Bridge to Terebithia, The Chronicles of Narnia. I remember feeling grown up when my sixth grade teacher handed me Gone With the Wind.

I would read anytime and anywhere. While eating breakfast at the dining room table. Sitting on the swings. Walking through the halls at school. In the car on road trips. At dinner, much to my mother’s frustration. In bed, under the covers, with a flashlight. Books were my first love.

During summer vacations, my mom took me and my sisters to the library weekly. Even though I lived in a tiny town with a tiny library, this was my favorite day each week. I read entire series and genres. One summer I worked through all the Nancy Drew and Hardy Boys novels; another summer I read every Zane Grey and Louis L’Amour book I could get my hands on.

At the age of 12, I was asked to work in the school library, in place of a study hall. I kept the job for two years, and loved every moment of it. I had many tasks, from putting away returned books to checking people out. The fun of stamping the “return by date” cards remains in my memory to this day. (Ask me how this is related to Indiana Jones someday.)

Working in the library was my first introduction to indexes. Not just the index at the back of the book, either. There was the venerable and mysterious card catalog.

The words “Dewey Decimal system” still lead me to link numbers and subjects. 920’s are biographies, for example. (I remember reading George Armstrong Custer’s at least three times. I went through a biography phase.) Every book is assigned a number – a class and a section. Call numbers can be assigned. To find a book, you need to know some information about it – you need to traverse the index!

Think of the Dewey Decimal classification for a book being a clustered index. It’s unique for each book, and tells the librarian what order it should be placed in on the shelf. When looking for a specific book, you look it up in the card catalog, then walk to that area of the library – no wandering around, looking through piles of random books (like heaps!).

But what if I want to find all books by a specific author, or on a certain topic? The card catalog gives you cards organized by last name, and topic, which are like nonclustered indexes. If you were looking for a book written by me, you could flip through “B” to find “Borland”. My card would include the book title, topic, and the number. This is essentially a nonclustered index – it points to the clustered index, where I can find the book on the shelf.

Just like a library would be a mess and books would be almost impossible to find without the Dewey Decimal system, the information in your database is going to be much harder to find – and slower to return – without indexes.

Want to learn more about indexes? It’s one of our favorite topics here at Brent Ozar Unlimited®! Visit our Index This page for more resources!