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.

ssmsreport 1 SQL SERVER   SSMS: Server Dashboard Report

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.

ssmsreport 2 SQL SERVER   SSMS: Server Dashboard Report

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.

ssmsreport 3 SQL SERVER   SSMS: Server Dashboard Report

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.

ssmsreport 4 SQL SERVER   SSMS: Server Dashboard Report

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

packagefail SQL SERVER   Fix Error   Package Microsoft SQL Management Studio Package failed to load in SQL Server Management Studio

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

balance SQL SERVER   Turning On Graphical Execution Plan After Enabling ShowPlan TextThis 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:

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

Method 2: Configuration Properties

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

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

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

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

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

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

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

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

SQL SERVER – Find Anything in Object Explorer in SSMS

This blog post is a follow up post of an excellent write up by my friend Vinod Kumar. I suggest you read his blog post here SSMS Tips–Object Explorer Details. After reading his blog post, I called him up and had an interesting conversation with him. Here is our conversation:

Pinal: It is easy to find anything in Object Explorer but it is not possible to find everything in Object Explorer.

Vinod: What do you mean? Explain me with example.

Pinal: For example, if I want to find anything related to any schema or table, I can go to Object Explorer and can search about that schema and find relevant information.objectsearch1 SQL SERVER   Find Anything in Object Explorer in SSMS

However, if I want every single object where any string exists, how do I find all of those objects.

Vinod: That is easy as well, remember SQL Server is Microsoft product and will follow the simple rules of the search which you are familiar. Just try to wrap your string with % and see the magic.

Pinal: Never thought of it. Let me try.

After the conversation was over, I tried to wrap my search string with % and it works, indeed it works. SSMS retrieved every single object where the string appeared.

objectsearch2 SQL SERVER   Find Anything in Object Explorer in SSMS

Thanks Vinod. It is so cool that when we have questions we can reach out to experts.

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

SQL SERVER – Rename a Table Name Containing [ or ] Identifier in the Name – Part 2

Yesterday I posted article where we discussed how to rename a table name where identifier is part of the tablename. Read the blog post Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name. If the table name contains an identifier, it is not easy to rename a table, the default method will show an error as displayed below.

sp_rename '[]', 'ProjectA';

The above query will give us following error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘[]’.

renameerror1 SQL SERVER   Rename a Table Name Containing [ or ] Identifier in the Name   Part 2

This is because our table name contains Identifier [ as well as Identifier ]. One of the method was to rename table was to use Double Quotes around the identifier.

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

renameerror2 SQL SERVER   Rename a Table Name Containing [ or ] Identifier in the Name   Part 2

At the end of the blog post, I asked if there is any other way to the same task.

SQL Expert Parth Malhan answered in the comment with alternative solution where he demonstrates that we can use identifier around the name of the table and rename the column as well.

EXEC sys.sp_rename '[[]]]','ProjectA'

renameerror3 SQL SERVER   Rename a Table Name Containing [ or ] Identifier in the Name   Part 2

Thanks Parth, very cool trick!

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

SQL SERVER – Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name

Just a day ago, my old colleague sent me an email.

“I accidently renamed my tablename as a [], and now I am not able to rename it back with the help of T-SQL to its original name which was ProjectA. Is there any way to fix it?”

Very interesting question. If you want to rename your table name, you can use sp_rename procedure to rename your table. Here is an earlier blog post, I have written on this subject SQL SERVER – How to Rename a Column Name or Table Name. However, if you have [ or ] in the name, you can not straight forward use the SP and rename the table.

First, we will try our usual way to rename the table.

sp_rename '[]', 'ProjectA';

The above query will give us following error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘[]’.

renameerror1 SQL SERVER   Rename a Table Name Containing [ or ] in the Name   Identifier in the Table Name

This is because our table name contains Identifier [ as well as Identifier ].

If your table name contains identifier, and if you want to rename it, you should run wrap the tablename with double quotes (“) and use the sp_rename command. Here is the example

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

renameerror2 SQL SERVER   Rename a Table Name Containing [ or ] in the Name   Identifier in the Table Name

Let me know if you follow any other method to rename table when there is identifier in the tablename.

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

SQL SERVER – Check Database Level (IsNullConcat) and Session Level Settings (CONCAT_NULL_YIELDS_NULL) using T-SQL

Earlier I wrote a blog post SQL SERVER – A Quick Note on CONCAT_NULL_YIELDS_NULL and in follow up to the blog post, I received few questions. Let me try to answer those questions in following blog post.

Q: How do we know if setting which returns NULL when concated to another NULL value is ON at database level?

A: You can run following script to identify the settings of the database level. In my script I have described AdventureWorks database and is checking if the settings which returns NULL when any other value is concated with NULL or not. If the query returns 1 that means when any value is concated with NULL it will return NULL. If the query returns 0 it means when any value is concated with NULL, it will still return original value and impact of NULL will be ignored.

-- Check Database Level settings
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsNullConcat');

Q: How do we know if setting which returns NULL when concated to another NULL value is ON at session level?

A: You can run following script to identify the settings of the session level. If the query returns 1 that means when any value is concated with NULL it will return NULL. If the query returns 0 it means when any value is concated with NULL, it will still return original value and impact of NULL will be ignored.

-- Check Session Level settings
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL');

Additionally, you can read comment of  Manigandan as he has demonstrated that we can also get the impact of function ISNULL with using another function COALESCE.

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

SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes

Have you ever faced situation where you have just created or modified object but SSMS still shows the error. I quite often face this situation where I come across situation where my SSMS Intellisense Cache is not refreshed or updated. This is indeed very frustrating when you are presenting something on stage as the red underline means an error in graved in many people’s minds and it is hard for them to believe when the code with underline runs successfully.

Here is image of the recent situation. Where I had just dropped index but SSMS Intellisense was still showing that the index exists.

refreshintel SQL SERVER   How to Refresh SSMS Intellisense Cache to Update Schema Changes

When I face this kind of situation, without wasting time I immediately do following : CTRL + SHIFT + R . This will clear the cache of the Intellisense and it removes the underline from the word. You can also refresh the Intellisense cache by using Edit -> Intellisense -> Refresh Local Cache.

Here is another question back to you – do you use SSMS Intellisense or any other tool?

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

SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question –

“I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?”

Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image.

sqlcmd ex1 SQL SERVER   How to Set Variable and Use Variable in SQLCMD Mode

Now in query editor type following SQL.

:SETVAR DatabaseName “AdventureWorks2012”
:SETVAR SchemaName “Person”
:SETVAR TableName “EmailAddress
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);

Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters.

sqlcmd ex2 SQL SERVER   How to Set Variable and Use Variable in SQLCMD Mode

Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily.

If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit.

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