SQL SERVER – SSMS: Scheduler Health Report

Have you ever been to a music concert? It is the most humbling experience one can get as a music lover. The live music with hundreds of musicians in one stage brings goosebumps with the very thought. I have always been fascinated and wanted to experience this once in a lifetime and am sure that day is not far off. What strikes me big time is the conductor, standing alone with a small stick orchestrates these fabulous musicians to give all of us a delight and experience of a lifetime. This brings me to today’s topic of discussion on the Scheduler Health Report. In a way the conductor inside SQL Server is our scheduler – who makes sure all the activities and all parts get their share of time to execute. Looks like a dream job, but trust me there is lot of effort in understanding how each components works just like how a conductor really knows when to introduce a new instrument as part of the concert. Before I start explaining about this report component, it’s important to go through the basics of SQL Scheduler which would help in understanding this report.

SQL Server has a mini operating system which manages the resource by its own, that’s why you would hear term called SQLOS. By resources we mean CPU, Memory and IO available on the machine.

Whenever any request is received in SQL Server, it would be assigned to a thread and that thread would be scheduled on a scheduler. This might go to multiple scheduler in case of parallelism though. Those threads which are ready to run would be scheduled and sent to the operating system for execution. Imagine a situation of blocking where a blocked thread can’t do anything unless the resource is available. In such situation, does it make sense to send this request out to the operating system for scheduling? Of course not! That’s why this mini operating system does better scheduling and SQL can scale up very well as workload increases. Another advantage of the SQLOS layer is to reduce context switching of threads in operating system because it only sends the threads which can do some meaningful work.

To summarize, SQLOS is a mini operating system within sqlservr.exe process which takes care of managing CPU, Memory, locks, IO and a lot more. In general terms, the scheduler is a logical CPU on SQL Server side. In general, each scheduler is mapped to one logical processors exposed by the operating system. There are hidden and visible scheduler in SQL Server. They can be looked into via DMV sys.dm_os_schedulers

To know more about our Scheduler, here is the place to get this report. Right Click on Server node, choose Reports > Standard Reports > Scheduler Health.

The complete reports revolves around SQLOS. SQLOS has something called non-preemptive scheduling (also known as cooperative scheduling) which is different from the scheduling done by the operating system. Windows operating system does preemptive scheduling where a thread would get a fixed amount of time to run on the CPU. Once the time slice is completed, it would be snatched out of the CPU and put into the queue for the next chance to run. This is a fair game because all threads are getting a chance to run. On the other hand, in SQLOS a thread would do its work and come back to the scheduler by its own; no one is going to take him off the scheduler. This term is called as yielding. If a thread went out from SQLOS layer and didn’t come back – it’s called as non-yielding situation. If all schedulers have the same problem of non-yielding them you can imagine that SQL would go to “hung” state. A scheduler can be in three states – Idle (when work_queue_count <> 0), Hung (when yield_count is not changing) or Active (when it’s not in the other two states). Hence the first part of our report shows which states our Scheduler currently is in. In our case, the scheduler is in the Idle state.

The second part of the report shows the details about worker (can also be accessed via DMV sys.dm_os_workers), tasks (accessed via DMV sys.dm_os_tasks) and processes running under each scheduler. Let us understand these terms in the little details as it would help you in understanding this section of report better.

Task – represents the work that needs to be performed. It can also be called as unit of work that is scheduled by SQL Server. An example of task could be pre-login, login, query execution, logout and may more. The task can be in various states (PENDING, RUNNABLE, RUNNING, SUSPENDED, DONE or SPINLOOP). Please refer this for more details.

Worker - are the threads who would do the task given by the scheduler.
Request – is the logical representation of a request made from the client application (or work done by system threads) to SQL Server. This request would be assigned to a task that the scheduler hands off to a worker to process.

Now that our fundamentals have been sorted, let us have a look at the second report section:

My machine has currently has 8 logical processors and hence we are seeing values of Scheduler ID’s: 0 to 7. Other schedulers are having status as “HIDDEN ONLINE” in sys.dm_os_schedulers. Each scheduler has various workers associated. We can see that in column #Workers for each scheduler rows. Once we click on (+) for the scheduler, we can see details about each worker. Further clicking on (+) for each worker, we can see the work done by that worker.

I hope that this blog has helped you in understanding the basic functionality of SQLOS and how the Scheduler Report drills into the fine prints.

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

About these ads

SQL SERVER – SSMS: Schema Change History Report

The heat is picking up and I am glad you are liking this series going so far. This particular report is close to my heart and the most recommended. At my recent trip to Delhi and the user group out there, I had the opportunity to meet a number of young DBA’s who were getting into their professional career at various organizations. I always try to persuade such groups with interesting questions to make them inquisitive about learning new concepts with SQL Server.

At this user-group session I wanted people to answer a simple question:

  1. How can I know, who created/dropped/altered the database?
  2. How can I know, who created/dropped/altered the objects?

This caught the attention of this group and I got various answers from DDL Triggers, Auditing, Error Logs, Extended Events and many more innovative response which I will refrain from disclosing his because they were really funny. All these answers were correct in a way and I had to counter them with yet another question to make them thinking.

Though your answers are correct in a way, “what is the easiest / simplest way to find this without writing a single line of code”. Now the twist made the response into something simple. And one attendee had a response stating – “why not use Profiler?”

This response stumped me totally and I said, let me achieve the same with lesser number of clicks for you. And my idea was to show them the use of SQL Server Management Studio – Schema Change History. It has interesting dimensions to examine and let me take a moment to walk them through the same.

Where to start?

The report location can be found from Server node -> Right Click -> Reports -> Standard Reports -> “Schema Changes History”.

One of the important information worth noting here is, the report fetches information from the default trace. We have talked about default trace and how to enable it in our previous post on “Configuration Changes History” report.

If default trace is not enable then this report is smart enough to look at each database and find objects which were created or altered in last 7 days. I was not aware of this until I disable default trace to see the error in the report. To my surprise, the report still came up in some different format. Let us look at the outlook with these options.

With default trace enabled

The report when the default trace enabled is as shown below:

To catch the query that populates this report, I ran Profiler and here is the basic query:

SELECT FROM:: fn_trace_gettabl(e @base_tracefilename, default )
WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

To elaborate a bit, the EventClass 46, 47 and 164 corresponds to Object:Created, Object:Deleted and Object:Altered respectively (Refer sys.trace_events from MSDN for more info).

With default trace disabled

Now here is the twist, when Default Trace is disabled, the query which is executed in each database is shown below for reference:

SELECT o.name AS OBJECT_NAME,
o.type_desc,
o.create_date,
s.name AS schema_name
FROM   sys.all_objects o
LEFT OUTER JOIN sys.schemas s
ON ( o.schema_id = s.schema_id)
WHERE  create_date > ( GETDATE() - 7);  

And below is the report generated by the query. It is evident from the last section of “Note” from the report that our default trace is not enabled.

There are two sections in the report. They are based on similar query which I pointed above with a minor difference of “create_date” and “modify_date” column as below.

SELECT o.name AS OBJECT_NAME,
o.type_desc,
o.create_date,
s.name AS schema_name
FROM   sys.all_objects o
LEFT OUTER JOIN sys.schemas s
ON ( o.schema_id = s.schema_id)
WHERE  modify_date > ( GETDATE() - 7);  

The disadvantage of disabling default trace is that we would not be able to see any information if a database was dropped.  I generally have seen this trace to be non-intrusive on most of the systems. But would love to hear from you and learn if you faced any problems with it.

Caveats with Schema Change History Report

One problem in the report is, even if one database is inaccessible, it would give error and fails to report anything for remaining databases. For illustration purposes, I made the database to norecovery state and I refreshed the report to get the below error:

If you ever have such situation, you can run the T-SQL query mentioned above manually on the database under question to view the changes.

Does anyone reading this post, ever disabled your Default Trace ever? Have you used this reports in your environment? Let me know your learnings.

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

SQL SERVER – SSMS: Configuration Changes History

As we go through each of the reports available in SQL Server Management Studio, I have an anecdote to share almost for most of them actually. I have used them extensively at different times and I am glad to share some of them via this blog series.

Once a friend of mine Joe pinged me over a chat. He was going to join a new organization and it was interesting to see the excitement and energy in his conversation. He was brimming with confidence about his new found job. After joining, he was back to ping me again. I was curious to know how his new job responsibilities were. After our initial greetings, Joe was quick to ask me a SQL Server related query.

Joe: Dave, in my new job we are supposed to have some standards to our servers.

Pinal: Ok, so what is the problem?

Joe: As I get access to some of the servers, I want to quickly know what changes were made to the server.

Pinal: Ok, so what is the problem? I still don’t get it.

Joe: Do you have any query that will give me this information?

Pinal: Now you are talking business.

Joe: I know you can help me, as you have done a number of times in the past.

Pinal: Thanks for your compliments, no need to search anywhere. It is already available with you.

Joe: You must be kidding me

Pinal: No Joe, Have you ever checked the Configuration Change history SQL Server Management Studio Report?

Joe: Ahhh that was a good catch.

Pinal: See, it was all along with you. Just that I helped you find it existed. Now enjoy!!!

To find this SSMS report, it is second in the list from Server node -> Standard reports.

The purpose of this report is to show two key information:

  • Changes made to Server Configuration using sp_configure
  • Changes to trace flags (Enable or Disable) done via T-SQL.

The information from this report is fetched from “default trace” which runs by default in every SQL installation. If the default setting is disabled by administrator by any chance, then we will not get any reports. We will get the below message in report when the default trace is disabled.

Currently, this table does not have any data to show, because default trace is not enabled.

To enable the default trace, below command can be used

sp_configure 'default trace enabled', 1
GO
RECONFIGURE WITH override
GO

For most installations you don’t need to enable because it is running by default. Normally, a user is likely to see the below report with data like:

I am always intrigued when such reports every time. To look at the query fired for this report, we can fire up profiler to capture the same. Report uses fn_trace_gettable to read default trace and get event class 22 and 116.

SELECT name
FROM sys.trace_events
WHERE trace_event_id IN (22, 116)

 

From the trace events, it means whenever a change is made using DBCC TraceStatus or sp_configure, they are recorded in default trace under “ErrorLog” and “Audit DBCC Event”.  To display the SSMS reports additional filters have been applied to show only relevant information.

Do check these reports in your environment and let me know if you found any interesting information that you learnt today?

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

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)

SQL SERVER – Fix Error – Package ‘Microsoft SQL Management Studio Package’ failed to load in SQL Server Management Studio

I recently formatted my computer and reinstalled my machine and I end up on following error:

Package ‘Microsoft SQL Management Studio Package’ failed to load

I was able to fix the error quickly, but while I was searching online, I noticed quite a few of the people have similar error and they struggle to fix it. I have decided to list a few of the ways to fix the error here.

Method 1: Reinstall .NET Framework

Method 2: Start SSMS with Admin privileges

Method 3: Reinstall SQL client with either command prompt or with wizard

Method 4: Delete the registry HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools and try to load the server again.

Let me know if there is any method which works to fix this error and I have missed it.

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

SQL SERVER – Turning On Graphical Execution Plan After Enabling ShowPlan Text

This may be surprising to many, but I have seen quite a few times so far so decided to blog over here.

Here is the sequence of the action:

  1. The developer turns on Graphical Execution plan for any query with CTRL+M
  2. Now turns on the execution plan in the text format with SET SHOWPLAN_TEXT ON command
  3. After this, the developer does his/her task to analysis the execution plan
  4. Now turns on the execution plan in the XML format with SET SHOWPLAN_XML ON command

Now when a developer has to turn back Graphical Execution Plan, he/she gets confused. We know that we can turn on TEXT and XML with the help of SET commands, but how to turn on graphical execution plans from T-SQL. Well, the matter of facts is there is no way to turn the graphical execution plan from T-SQL.

The only way to get the Graphical Execution plan back in SQL Server Management Studio (SSMS) is to turn off TEXT and XML plan and it will automatically turn on the graphical execution plan.

For example, in above situation when we have turned off TEXT and XML plan, set them off as described in the following steps

  1. Turn off XML execution plan with SET SHOWPLAN_XML OFF command
  2. Turn off TEXT execution plan with SET SHOWPLAN_TEXT OFF command

It will automatically turn on the graphical execution plan. Remember, if you have turned off either of XML or TEXT, you will need to turn off that one only. However, if you have turned on both of them, you will have to turn off both of them to get back your graphical execution plan.

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

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

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