sql server management

Get Filestream Storage Directory

by
Facebooktwitterredditpinterestlinkedinmail

Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system somewhere. But what if you want to get access to these files or if you just want to know where they’re stored. SQL Server provides a function called PathName that you can call to get this path. You call this function like you would call an extension method in .Net. In the example below, we’ll assume that the column InvoiceFile is a Filestream datatype.

SELECT	TOP 1
		InvoiceFile.PathName() AS DirectoryOfFile
FROM	Invoice

Sql Server Version

by
Facebooktwitterredditpinterestlinkedinmail

Here is a super simple query that will get you your current SQL Server version. Enjoy!

SELECT	'SQL Server ' +
		CASE	CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(100)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(100)))-1) AS INT)
				WHEN 8 THEN '2000'
				WHEN 9 THEN '2005'
				WHEN 10 THEN '2008'
				WHEN 11 THEN '2012'
				WHEN 12 THEN '2014'
		END	+ ' ' +
		CASE	CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(100))
				WHEN 'RTM' THEN ''
				ELSE CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(100)) + ' '
		END +
		CAST(SERVERPROPERTY('Edition') AS VARCHAR(100)) AS SQLServerVersion

SQL Server Port

by
Facebooktwitterredditpinterestlinkedinmail

The SQL Server port is 1433.

The client application port that connects to SQL Server is usually a random port between 1024 and 5000. This port on the client computer is what port SQL Server will use to send data back to the client with.

 
Firewall Considerations
If you are looking to setup a firewall to work with SQL Server ports, you will need to make the following settings:

  • Allow traffic from any port greater than 1024 to port 1433
  • Allow traffic from port 1433 to any port greater than 1024

SQL Server Management Studio Keyboard Shortcuts

by
Facebooktwitterredditpinterestlinkedinmail

Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.

Ctrl + R – Show/Hide the results pane

Ctrl + N – New query window

Ctrl + Shift + U – Switch current selection to uppercase

Ctrl + Shift + L – Switch current selection to lowercase

F5 – Execute the currently selected query (or entire query if nothing is selected)

Ctrl + Tab – Switch to the next open tab

Ctrl + Shift + Tab – Switch to the previous open tab

Ctrl + Space – Pops up IntelliSense complete if it is not already open

Ctrl + Shift + R – Refresh local IntelliSense cache

F8 – Open the Object Explorer if not already open

 

 What other SSMS keyboard shortcuts do you use?

Rebuild Execution Plan

by
Facebooktwitterredditpinterestlinkedinmail

One way that SQL Server optimizes stored procedure execution time is by caching the execution plan.  This functionality is built-in to SQL Server and is done every time that you create a stored procedure.

What Is An Execution Plan?

An execution plan in SQL Server is simply the way that SQL Server will go about getting the data for the client.  When a query is executed in SQL Server, it will generate an execution plan.  This execution plan is a set of steps that is the most efficient way to get this data from the database for the user.

How Do You Know If You Need To Rebuild An Execution Plan?

Okay, this is not very scientific.  Usually what I’ve found in the past is that when a stored procedure runs fast one day… and the next day it is running slow… one cause might be that the execution plan needs to be rebuilt.

How To Rebuild An Execution Plan?

If you think that a stored procedure in your database has an execution plan that is not the most efficient, here are a couple easy ways to rebuild the execution plan.  Keep in mind that the following queries do not actually rebuild the execution plan.  They merely force the stored procedure to rebuild the execution plan next time that it is executed.

Rebuild For One Specific Stored Procedure

In the example below, CustOrderHist is the name of the stored procedure we want to rebuild the execution plan for.

EXEC sp_recompile 'CustOrderHist'

Rebuild All Execution Plans For All Stored Procedures On Your Server

NOTE: The WITH NO_INFOMSGS option will suppress a generic message that comes up after executing the FREEPROCCACHE command.

DBCC FREEPROCCACHE WITH NO_INFOMSGS

Why Does This Happen?

As data changes in your database, the data in the index will change as well.  As the statistics for an index change, what used to be the fastest way to pull data may no longer be the fastest way.

Automatic Rebuild Of The Execution Plan

Of course SQL Server does not require you to always rebuild the execution plans manually.  There are triggers in the database that will cause an automatic rebuild of the execution plan.

  • Alterations to the table, view, stored procedure, or indexes used by the stored procedure
  • Updating statistics on the table or index that the stored procedure uses
  • Dropping an index used by the stored procedure
  • A lot of inserts or deletes from a table (causing a large change in the keys)
  • Executing a stored procedure with the WITH RECOMPILE option

Reference: http://technet.microsoft.com/en-us/library/ms181647.aspx  –  sp_recompile (Transact-SQL)
Reference: http://technet.microsoft.com/en-us/library/ms174283(v=sql.105).aspx  –  DBCC FREEPROCCACHE (Transact-SQL)