SQL SERVER – Free intellisense add-in for SSMS

This article shows how to use ApexSQL Complete, a free SQL Server intellisense add-in. You can download ApexSQL Complete, and play along through the article.

ApexSQL Complete is a free SQL Server Management Studio and Visual Studio add-in that speeds up SQL coding. In this article, we will explain ApexSQL Complete through its features. To start using ApexSQL Complete, enable it from the ApexSQL menu under the main menu in SSMS:

Hint list – complete your SQL code

This is a key feature of ApexSQL Complete. It helps you find the wanted object or a SQL keyword, and complete the SQL statement faster instead of typing the entire object name or keyword. After you start typing, for example “SE”, SQL intellisense will appear with all keywords and database objects that contains “SE”, listed by importance:

You can uncheck the box for the appropriate objects, and prevent them to appear in the SQL intellisense from the add-in options, under the Hints tab. This will decrease the number of hints in the SQL intellisense list, and speed up the coding process:

Another useful property of the SQL intellisense in ApexSQL Complete is a multiple sections, which allow you to navigate over the database schema, to a table, and select the specific columns, checking the appropriate boxes. You can also select the columns and hit the Enter key to insert them:

If a SQL script you are using is large, at some point you will need to look at SQL code before you continue. ApexSQL Complete offers you to accomplish this without a break. Press and hold the CTRL key, and the SQL intellisense will become transparent, so you can see through, and review SQL code. Releasing the CTRL key will get you back to a previous state, and you can continue typing:

Tab navigation – monitors all opened SSMS tabs

This feature allows you to track opened and recently closed tabs, or to restore previously saved session after crashing. These operations can be managed from the add-in options, under the Tab navigation tab. Here you can set the period for keeping the tabs saved, and set the interval for auto-save.

This could be useful if, for any reason SSMS crashes. The Tab navigation feature saves your time, and gets you back to a point before crashing.

The Tab navigation pane consists of two parts, Opened tabs and Recently closed tabs. In the Opened tabs section, all opened tabs from one session will be shown, and you can easily switch from one to another. You can search the content of the opened and closed queries, and open the query that contains searched results. Double-clicking the query from a list of the Recently closed tabs tab opens it in a new query window. For the opened and closed tabs, if you select a query from the list, a complete content will be shown in the preview section to the right:

At any point, you can save your workspace or opened tabs, and restore to the saved state later, if SSMS crashes.

Code structure – view and find SQL code blocks

This feature provides a tree-like form of SQL code presented in a separate SSMS window on the left side of the query window. When you enable the Code structure feature from the add-in options, it allows you to see all the important parts of SQL code used in the query. SQL code blocks from the Code structure window can be expanded so you can navigate to a specific part of the block. Selecting any item from the Code structure window highlights a SQL code block in the query window:

This way you can move through SQL code in blocks and find the part you are looking for instead of scrolling down the query.

Executed queries – track executed queries

Using this feature allows you to track all executed queries in a defined period. To enable the feature, select the Log execute queries option under the Executed queries tab in the add-in options. You can set the folder for storing the executed queries. The queries are saved as an .xml files. You can also define the maximum number of lines in SQL code, which will be stored. The Default period option allows you to show the queries executed in a defined period.

When activated, the Executed queries form will show all the queries executed in a defined time range. If you select a query from the list, its content appears in the preview section. You can search through the queries, executed in a defined period. Double-clicking any of the executed queries from the list opens it in a new query window in SSMS, so you can additionally change SQL code.

Snippets library – insert often SQL statements

With this feature you can insert often used SQL statements, even a whole procedure, or blocks of SQL code. You can create a snippet from the ApexSQL Complete options, or from the SSMS query window:

1) To create a snippet from the ApexSQL Complete options, navigate to the add-in options, and click Add new snippet option, under the Snippets tab:

Here you can edit any of the predefined snippets from the library, export/import them to use it on another machine.

2) To create a snippet from the SSMS query window, type SQL code you want to be defined as a snippet, select it, and right click on it. From the context menu, choose the New snippet option:

This will open the Create a new snippet window, with the selected code already inserted in the Code section. You just need to define a name for the new snippet, and optionally a description:

To use already created snippet from the Snippet library, click the Insert snippet option from the context menu in the SSMS query window, and double click on a snippet from the list to use it in the query.

Navigate to object – locate an object in the Object Explorer

This allows you to locate the selected object in the Object Explorer pane. In the query window, select the object you want to locate, and right-click on it. From the context menu, choose the Navigate to object option, and the selected object will be located and highlighted in the Object Explorer pane to the left.

Test mode – execute queries without impact to the database

The Test mode feature allows you to execute a query in a test environment, without impact and consequences to the database. To use the Test mode feature, select the Test mode option from the toolbar, and highlight SQL code in the query window you want to execute.

The Test mode feature will add BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements. After the execution, it rolls back the transactions at the beginning:

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

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)

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:

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.

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

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.

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'
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'
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.

xp_readerrorlog 0, 1, N'Server is listening on'

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)

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.

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.

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 ‘[]’.

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.

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'

Thanks Parth, very cool trick!

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