SQL SERVER – SSMS: Server Dashboard Report

The term dashboards come from the automobile world where it meant as instrumentation or controls for operating a vehicle. In the IT parlance, the use of dashboards meant as the state or health of system or organizations. Though this analogy cannot be taken too far because running a business is not same as running a vehicle. But the similarities of knowing the health of the system is bang-on.

In our previous post, I gave an introduction on where to find all the SQL Server Management Studio reports. Today we will look at the first and the most basic report all of us must be working with. It is called as “Server Dashboard” report under the Server node, standard reports node.

This report gives and overview of various settings in a SQL Server instance. As the name suggests, it shows the health of SQL Server instance level and has three sections to examine. We will investigate each of these sections next.

Configuration Details

This section is interesting because it can answer some interesting questions:

  • When was the SQL Server started / restarted?
  • What Edition of product are we running and version?
  • If the instance is a Clustered instance or not.
  • Number of logical processors used by the SQL Server instance.

When someone asks me to performance tune a system, I generally tend to ask some of these basic questions to customers. Lesser known to DBA is that instead of querying or relying on their Excel sheets, this report can give this information in an instant.

Most of the other values are self-explanatory. It should be noted that “Scheduler Agent Jobs” shows the number of jobs created, not what is running or enabled.

Non Default Configuration Options

Let us move to the next section. As an administrator, if you want to know the server level settings that were changed from default in a given instance, then this is the section to note.

By default the values are not visible. We need to click on (+) symbol to toggle it.

This part of the report can show various non-default configuration settings done via the executing sp_configure stored procedure. You can read about enabling / disabling sp_configure basics from my previous blog posts.

I always look into this section to see if the administrator or someone else in the system has limited the memory allocated to SQL Server on a given box. This comes under the “Max server memory(MB)” section as shown above.

This section also shows if any trace flag is enabled on the server. This information is normally not available when you just play around with sp_configure.

Activity Details

Next the busier part section with Activity Details come. This has a number of interesting information. A visual graph to show the CPU and IO performed on the instance. But most importantly there are sections on the top as a table which is worth a note.

  1. Shows the number of Active sessions and transactions in the system at the moment.
  2. Active databases available in the instance.
  3. Total server memory in the system.
  4. Logins connected to the server.
  5. Number of blocked transactions in the system
  6. Finally, worth to note is the server side traces running in the system.

Though the information is available in a nice report, the same can be got via various DMVs from SQL Server. I am outlining some of these for reference.

Title DMV and filter
Active session sys.dm_exec_sessions where is_user_process = 1 and status = ‘running’
Active Transactions sys.dm_tran_active_transactions where transaction_state = 2 or( transaction_type = 4 and dtc_state = 1)
Total Server Memory sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
Idle Sessions sys.dm_exec_sessions where is_user_process = 1 and status = ‘sleeping’
Blocked Transactions sys.dm_tran_locks where request_status = ‘WAIT’
Distinct Connected Logins on Sessions sys.dm_exec_sessions where is_user_process = 1
Traces Running sys.traces where status = 1

The query to generate the graph is shown below. This is based on historical data using sys.dm_exec_query_stats to show the graph for IO and CPU.

Query 1:
SELECT  total_worker_time,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME,
dbid
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Query 2:
SELECT  total_logical_reads + total_logical_writes AS total_io,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Though the reports are a great way to see this data visually, it is nice to see the power of DMVs and how they can be used with SQL Server. All these information are available out-of-box but these are visually represented nicely using the SQL Server Management studio dashboards.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Introduction to SQL Server Management Studio Reports

I get a ton of questions over mail and one of the most theme is always around Performance tuning and troubleshooting. To add to this confusion, even my chat window mostly involve around talking about some performance problem with SQL Server. Let me tell you some of these common themes or questions:

  1. How to improve performance?
  2. How do I know problematic queries?
  3. What is consuming my space in HDD?
  4. How to find out CPU hogging queries?
  5. Who dropped my tables / database?
  6. Who is consuming my memory on this SQL Server?
  7. When was the backups / restores happen on this Database?
  8. I am can see a lot of IO activity on my production server with 50 databases, I don’t know who is causing the IO now?
  9. What is the Index usage statistics?

And this list keeps going on and on. For most of the queries there are my pluralsight courses on performance that you can watch or there are tons of scripts over this blog itself that can help you answer a number of above queries. I will refrain from linking all of these in this one blog but we will look at a different handy utilities available with SQL Server Management Studio out-of-the-box.

In this era of obsessed enterprises, the CxO are always wanting to look at the health of their systems in new and innovative ways via Dashboards and reports. This is sometimes like driving a vehicle, you know the health of the vehicle by watching its dashboard for speed, fuel, rpm or any other warning indicators. So to run a business the executives rely on these reports from their teams. In a similar way, a lot of DBA’s just wish they were empowered with such capabilities for their day-today lives too. Lesser known to them is that since SQL Server 2005, such reports have been added out-of-box to them at their fingertips.

Reports: Where to find them?

There are a number of reports available with SQL Server Management Studio and they can be found at two locations:

  1. On Server Node -> Right Click -> Select Reports -> Standard Reports
  2. On a specific Database -> Right Click -> Select Reports -> Standard Reports

In this example, we will be using a SQL Server 2014 instance. Under the Server node we can see close to 23 different reports.

Under the Database node, we can find close to 18 different reports to work with.

Administrators Reports Center

Now that we have got to know these reports existing on SSMS. It is worthwhile to explore some of them. This series will be to explore these hidden gems and how one can use them effectively in their day-today lives.

All the reports have some use and help administrators solve basic questions like the one asked before. The simplest I can think today is around the questions of database:

  1. What is my database disk usage
  2. Disk usage by Tables, Partitions
  3. What are the transactions blocking and much more.

Recently a Developer caught me at the local SQL Server Bangalore UG and asked if they can modify the same. Interestingly, these reports are not stored as plain vanilla .rdlc files but in some other internal formats. Having said that, it is always possible to create our own custom .rdlc files and use the same with SSMS. In this blog series, we will look at functionality of the majority reports that a DBA must be aware and how we can maximize our time by being productive with these reports. Hope you will enjoy this journey with me.

PS: If you have been using these reports in your environment, can you let me know the reports that you use most often and why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Blocking Using Blocked Process Threshold  

In my previous blog I was talking about my friend Jim who was on a consulting assignment and was asking me for some help around blocking. Though the tip mentioned was of help, Jim did reach out to me the day after and had more queries. Some of these sound simple, but you will be surprised how some of the out-of-box features are so hidden within the product of SQL Server. Let me give you a glimpse of our chat and what we ended up as technology discussion.

Jim: Hi Dave.

Me: Hey Jim, how are you? Have you started your assignment?

Jim:  I am doing great and the tips you shared last time were quite handy. But there is a problem in the approach though.

Me: You must be kidding, Activity Monitor didn’t work?

Jim: No, you got me wrong Dave. It is totally a different problem.

Me: Well, I am all ears and listening – go on.

Jim: The solution of Activity Monitor was awesome, but this finance based system of SQL Server is running round the clock. And there are problems that surface once in a while during the night.

Me: Ok, so what is the problem?

Jim: I need a mechanism to monitor or identify problems proactively and not in real time as-in using an Activity Monitor window in SSMS.

Me: Hmmm interesting, but the solution is easy then.

Jim: Dave, you must be kidding. Just to make sure, I was genuinely searching your blog this time before pinging you.

Me: Haha – you really took it to the heart buddy. Give me a second.

Jim: Sure, take your time – “I am waiting”.

Me: In a minute – Thanks to you Jim. For a matter of fact, I haven’t written on this topic though I have a demo that I show during my sessions.

Jim: Is it?

Me: Now that you brought this thing up, let me show you the steps. It is called “Blocked Process Threshold”.

It takes less than 5 minutes I showed the demo and we were done while Jim went back happy for a good night’s sleep. Below are the steps I used in my demo to
Jim:

Configuration Steps

The concept of Blocked Process Threshold was introduced with SQL Server 2008. The real reason is to identify if a query is waiting for a resource too long and as an administrator we want a mechanism to capture such waiting queries. The first step to configuration is to enable the server settings.

What does the command say?

sp_configure'blocked process threshold',10;

The above command means when a query waits for more than 10 seconds, raise an event. As simple as that – Now this event can be captured using profile and we can take action accordingly.

Configure Profiler

The first step is to configure profiles events. Open Profiler, give the credentials to connect and go to the Events Selection Tab. Under “Errors and Warnings”, select “Blocked Process report”. This is shown in the diagram below.

Now select “Run” and we are all set. Now based on the configuration we made, if any query is waiting for a resource for more than 10 seconds then an event is raised and this is captured in our profiler trace. We can also run it like a server side trace, but that will be for a different blog post. Below we can see a “Block process report” being generated and it will generate an event every 10 seconds until the query goes succeeds.

Reset script

If you like to reset this value and not generate the Blocked Process report, then set the server setting to 0. The command for the same will be:

sp_configure'blocked process threshold',0;

Let me know if you have ever used this feature in your environment and how handy this has been to your developers and administrators.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Developer’s Life – Every Developer is a Iron Man

Iron Man is another superhero who is not naturally “super,” but relies on his brain (and money) to turn him into a fighting machine.  While traditional superheroes are still popular, a three-movie franchise and incorporation into the new Avengers series shows that Iron Man is popular enough on his own.

As a superhero with a lot of tools, a lot of technology, and a lot of smarts, there are some pretty clear connections we can draw between Iron Man and developers.

So how developers are like Iron Man?

Well, read on my list of reasons.

Choose Your Bettles

Iron Man chooses his battles wisely.  He doesn’t fight every super villain, or wait in dark alleys for small-time crooks, he goes after the specific enemies.  Developers also know that no system is going to be perfect, and to focus on “battles” that will help solve the most immediate problems.

Do not Give up

Iron Man doesn’t give up, even when he has the most ridiculous odds stack against him (that just makes the final battle scene even more exciting).  Developers are also known to be stubborn and never give up until they have conquered a problem.  There is a reason they seem so addicted to caffeinated drinks!

Keep on Improvising

Iron Man is always trying to improve himself (and his suits).  Significant parts of the second and third movies, in fact, were set in his workshop as he tinkered with better suits.  Developers might regret that there is no “super suit” they can put on that will solve all their server issues while they take a nap – they ARE the suit.  They are always learning an discovering new powers of their own.

A Lone Hero

Iron Man can be a loner.  He works alone in his shop, with only a robot (with a British accent) as an assistant – and has built some of the coolest technology in the superhero universe.  Developers also come up with amazing things on their own through hard work and inventiveness.

A Team Player

Iron Man also knows he needs a team.  While this famous loner tries to ignore outside help, he also swallowed his pride and joined the Avengers.  No matter how much developers may like working on their own, they also know they have to cooperate with a team to come up with even bigger solutions.

Have Fun

Have Fun.  Not once does it appear Iron Man is not enjoying himself – and since his alter ego is billionaire Tony Stark with a multimillion dollar company to run, if he weren’t enjoying himself he could always quit and live an (almost as) exciting life.  Developers may or may not have the freedom to quit their job at any time, but I know many who would not quit even if they were millionaires, they love their jobs so much.

I think that many more pages could be written about Iron Man/Tony Stark, but instead, I think we should all go watch the movies again.  What other similarities do you see between our favorite “genius, billionaire, playboy, philanthropist”?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Add a Column at Particular Ordinal Position in Table

Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.

Let us consider the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command

ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;

Now the new column named modified_date will be added after item_id. You can check this using the following command

Field		Type	Null	Key	Default	Extra
--------------------------------------------------------------
sales_id	int(11)	NO	PRI		auto_increment
item_id	int(11)	YES			
modified_date	datetime	YES			
sales_date	datetime	YES			
sales_amount	decimal(12,2)	YES

This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries

Recently when I was busy working on my next course for Pluralsight, one of my friend pinged me for some help over messenger. And the conversation went on a different spin, here is a gist of what we talked and I felt this was worth a share

 Me: Hi buddy, what’s up?

Jim: Dave, I have an unique problem and thought you can help me.

Me: Sure, let me know how I can be of help.

Jim: I am going on a quick consulting assignment tomorrow and I am wanting some helping hand.

Me: Hmmm Sure, let me know what the problem is?

Jim: This customer has a lot of deadlock and blocking problem. And I wanted to know if you know any easy steps to find blocking queries in a real time system?

Me: Hold on let me check.

Jim: Sure.

Me: Ahha Did you check my script available over my blog?

Jim: Awesome, Pinal I think you saved me a day. And apologize for not doing a quick search over your blog. But Pinal, I have one doubt.

Me: On the script?

Jim: No Pinal, the script was written 4 years back and is still a gold mine to help me tomorrow. I am just curious, have you ever explored any alternatives? I am sure your exploration wouldn’t have stopped.

Me: Well Jim, there are a number of other scripts but the one of the hidden gem I don’t think I have written over this blog is around Activity Monitor.

Jim: Oh is it? Can you show me what you are saying?

We get over a screen sharing and I show couple of neat tricks available with Activity Monitor. Let me take a moment to pen down what I showed Jim yesterday.

The quickest way to initialize the Activity monitor is to use the shortcut (CTRL+ALT+A) inside Management studio or feel free to read this blog on where to find the Activity Monitor.

Activity monitor to identify Blocking

The activity monitor has a number of tabs and the tab under question is the “Processes” tab. If you have a blocking scenario then activity monitor can be really a quick solution.

In the image below, we can see SPID of 51 is head of the Blocker and SPID of 55 is waiting for locks to be released by 51. We can also find the wait time, resource wait type and more from this screen.

As a DBA if you want to kill SPID 51, we can quickly do that from Activity monitor using the right click menu as:

When I showed this to Jim, he was super excited to see and was all awe of what we can do just with SQL Server Management Studio. So Jim quizzed me to show him one more tip using Activity monitor as a challenge. Since he was going for a performance troubleshooting exercise, I thought of sharing one more cooler tip using the Activity monitor.

Activity monitor to find execution plans of expensive queries

I asked Jim how he would find out if there are performance problems for a given query. The obvious answer as an expert was to use an execution plan. The problem of such an approach is that when the query executed with parameters, then when we pull them out from Profiler or other mechanisms based on Reads, Write or CPU usage – the parameters need to be replaced, executed and then we need to view the execution plan.

Interestingly, Activity monitor has an interesting tab called as “Recent Expensive Queries”. These are queries based on Reads, CPU, writes etc. Once you get into this screen, right click the query under question and select “Show Execution Plan”.

End of the screen sharing exercise and about 10 mins of quick talk with me, Jim was super excited and said he will ping me once the assignment is over and how he used some of the tips I shared with him.

Activity Monitor is an awesome hidden gem inside SQL Server Management studio that many fail to use for quick monitoring. Hope this blog post inspired some of you to use it in your environments in the future. Do let me know if you ever used Activity monitor in these ways.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSIS Component Error Outputs – Notes from the Field #034

[Notes from Pinal]: Error confuses people and stops the operations. Developer search more on error online rather than best practices. Lots of people think that SSIS is all about arranging various operations together in one logical flow. Well, the understanding is absolutely correct, but the implementation of the same is not as easy as it seems.

Linchpin People are database coaches and wellness experts for a data driven world. In this 34th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to understand more about SSIS component errors.


When working in the SSIS data flow, you’ll notice that many sources and transformations and some destinations have a built-in output to handle errors.  The error output allows the SSIS developer to create a separate path through which error rows can be directed.  In this post, we’ll briefly discuss the essentials and design patterns for using error outputs on data sources and transformations.

Overview

Error outputs are a secondary path through which the data flow can send rows that do not conform to data type, length, or transformation standards defined by the ETL developer.  That’s a lengthy way to say that it’s where you can send your junk data.  In the SSIS designer, clicking on a source or transformation will often show not one but two possible outputs: the primary output (the “good” data, indicated by the blue line) and the error output (identified by the red line).  As shown on the flat file source below, when selecting a source or transformation, those that have an available error output will appear with both output connectors ready for selection.

Using this design will allow you to handle errors such as:

  • Incorrectly typed data
  • String data longer than its specification
  • Invalid transformation logic
  • Relational constraint errors at the destination

Error outputs provide a great way to handle two things at once: providing a secondary path through which bad data can be either triaged or cleaned up, and preventing the package from failing based on just a few bad rows of data.

When connecting an error output to a downstream component, you’ll be prompted with a configuration window in which you can select the error output options.  The most important thing to remember here is that default behavior is to fail the component upon any error, even when you connect the source or transformation’s error output.  The default options are shown below.

As mentioned, you must explicitly set the behavior to Redirect row for the column(s) that will be handled by the error output.  You can do this on a column-by-column basis, if you want to handle error conditions on that level, but in most cases I see that all columns are set to either fail or redirect as a group.  In the screenshot below, I’ve set all columns to redirect upon error or truncation.

Practical use

So what happens when we connect a component’s error output?  In most cases, any rows in error would be redirected to that output, which we can use as a secondary path for cleanup or triage.  Assuming we change the error and truncation behavior to Redirect row, errors that are captured by the error output would not result in a failure of that source.

There are several design patterns surrounding the use of error outputs on sources or transformations.  Among the most common:

Cleanse inline.  If possible, the data can be sanitized inline using other SSIS components.  If the ETL can be built to handle most of the known data deficiencies, this is usually the cleanest way to handle error rows.  Very often, data quality tools such as SQL Server Data Quality Services can be useful for inline cleansing.

Send to triage.  If the package does not have cleansing logic, or there are rows that still cannot be cleansed using that logic, the bad data can be sent to triage for manual review or cleansing.  Though this pattern does require manual intervention, it also allows us to audit and review the reasons for those failures by preserving the data.

Ignore. It is possible to redirect error rows into the bit bucket, resulting in a loss of data.  In rare cases, this is acceptable; however, in most cases, there should be some measure of data capture to improve the quality of the ETL process.

Which is the best pattern to use?  As always, it depends.  Factors such as the criticality of the data, the types of errors expected, the frequency of the ETL process, and many others must be used to decide how, if at all, to use the error paths in SSIS.

Caveats

As with any ETL function, there are a few caveats and cautions around using error outputs.  First of all, I don’t recommend adding an error output path to every component that supports it simply because it’s available.  Redirecting error rows is not always appropriate – sometimes it makes sense to fail the package rather than trying to programmatically clean up or triage error data.

Additionally, you’ll need to familiarize yourself with the different types of error outputs generated by each component.  For example, if you use the error output of the flat file source, it will not break apart the data into individual columns (even for those elements not in error for that row) – because the error output is a relatively low-level function, what you get is a single string with all of the data in it, and if you have processes that consume this data downstream of the error output, you’ll need to do some transformation on the errored rows before you can do any cleanup.

Finally, it is important to note that not all components support error outputs.  Because of the differences in the types of operations performed by each source, destination, or transformation component, some of them do not include an error output path.

Conclusion

In this post, we’ve briefly covered the error output facilities for the SSIS data flow, along with a few design patterns for implementing these in a package.  In a future post, we’ll talk more about implementing an end-to-end solution for handling error rows in an SSIS data flow.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)