Show\Hide results pane in SQL server management studio

To change keyboard shortcut for show/hide results pane, go to Tools Options in SQL Server Management Studio:

Navigate to Environment > Keyboard > Keyboard.
Search for “Window.ShowResultsPane” using “Show commands containing” textbox.
In the “Press shortcut keys” textbox, press Cntrl + R buttons on the keyboard at the same time.
Click the “Assign” button and then click “OK“.

You might need to restart SQL Server Management Studio to apply these changes.

Continue Reading

Improve performance of T-sql statements containing encrypted columns.

We can use database level encryption to encrypt sensitive information. A commonly used level of encryption is to only encrypt columns that contain sensitive information like credit card numbers, date of birth etc. Encrypting and decrypting database level information is very CPU resource intensive. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.

We faced a similar performance issue in one of the previous companies. Database selects based on SSN were very slow as the database had to decrypt the whole column to perform comparison. If you look at the information below(under Current Problem) the database performed 4413 logical reads, 4406 read-ahead reads and took 302 milli seconds to perform the operation.

To improve the performance of these kinds of queries, we decided to store the last four digits of the SSN in clear text and index the column as a non-clustered index. First filtering the rows based on last four digits of the SSN and then filtering the rows based on SSN lead to a dramatic improvement in performance. f you look at the information below(under Improved Solution) the database performed 27 logical reads, 10 physical reads 10, 8 read-ahead reads and took 71 milli seconds to perform the operation.
All in all a huge performance improvement.

Current Problem:

begin
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

declare @ssn varchar(max) = '123123123'
with PersonalIdentifierInformationCTE as
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE where Identifier is not null
and Identifier=@ssn
end
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 4413, 
physical reads 3, read-ahead reads 4406, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 302 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Improved Solution:

begin
declare @ssn varchar(max) = '123123123'

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

with PersonalIdentifierInformationCTE as 
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier, SSNLast4
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE 
where SSNLast4<> 0 and SSNLast4 = substring(@ssn, LEN(@ssn)-4+1, LEN(@ssn)) 
and Identifier is not null
and  Identifier=@ssn
end
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
    (8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 27, 
physical reads 10, read-ahead reads 8, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
    (1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 71 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Continue Reading

Document database using SchemaSpy and Graphviz

SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. See here for more information.
Graphviz is open source graph visualization software. Graph visualization is a way of representing structural information as diagrams of abstract graphs and networks. See here for more information.
jTDS is an open source JDBC driver for Microsoft SQL Server and Sybase databases. See here for more information

We will use SchemaSpy, Graphviz and JTDS to document a SQL server database.

Step 1: Download Graphviz from the following location
Step 2: Open command prompt in Administrator mode. Change to the directory where the Graphviz msi file has been downloaded and run the

msiexec /a graphviz-2.38.msi

command. As of 8/4/2016, the latest version of Graphviz is 2.38. Run through the various steps of the Graphviz installer.
Step 3: Add the Graphviz bin folder path to the “Path” “system variables” in “Environment variables”. The path of the bin folder on my machine was “C:\Program Files (x86)\Graphviz2.38\bin”.
2016-08-04_22-16-49

2016-08-04_22-21-50
Step 4: Download Schema Spy from the following location. As of 8/4/2016, the latest version is 5.0.0.
Step 5: Download and unzip the latest JTDS drivers for SQL server from the following location. As of 8/4/2016, the latest version is 1.3.1.
Step 6: For the database being documented, make sure that TCP\IP protocol is enabled and the TCP\IP port points to the port name in step no 7 below(i.e 1433 in this case). This can be configured using “SQL server configuration manager” for SQL server database.

2016-08-05_0-13-07

Step 7: Restart database server using the services snapin.

Step 8: Run the following command to generate the documentation for the database.

java -jar schemaSpy_5.0.0.jar -t mssql05-jtds -db SqlTuning -host localhost 
-port 1433 -u sa1 -p sa1 -o SqlTuningDocumentation 
-dp C:\Downloads\jtds-1.3.1-dist\jtds-1.3.1.jar -s dbo -loglevel fine
Continue Reading

T-SQL Recipes Primer

Forcing sort order not directly supported by the data Eg: Retrieve only colored products and show red products first before other products.
Solution: The solution converts the value ‘Red’ into a single space, which sorts before all the color names. The CASE expression specifies LOWER(Color) to ensure ‘Red’, ‘RED’, ‘red’, and so forth are all treated the same. Other color values are forced to lowercase to prevent any case-sensitivity problems in the sort.

SELECT ProductID, Name, Color FROM Products
WHERE Color IS NOT NULL
ORDER BY CASE LOWER(Color)
WHEN 'red' THEN ' '
ELSE LOWER(Color)
END;

Present a result set to an application user N rows at a time. Eg: Skip the first ten rows and fetch the next five rows.
Solution: Make use of the query paging feature in SQL Server 2012. Use OFFSET to specify how many rows to skip from the beginning of the possible result set and FETCH to set the number of rows to return. You must specify an ORDER BY clause.

SELECT ProductID, Name FROM Product
ORDER BY Name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Query to find Nth highest salary without using SubQuery Eg: write a query to get the 5th highest salary.
Solution: Make use of the Common table expression to get the salaries along with a row number. Then select from the common table express to get the 5th highest salary.

WITH Salaries AS(SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'RowNum' FROM Employee)
SELECT  Salary fROM  Salaries 
WHERE RowNum = 5

Differences between common table expression and temporary tables(or table variable):
A common table expression creates the table being used in memory, but is only valid for the specific query following it. Common table expression will need to be recreated every time it is needed.
A Temporary tables(or table variable) is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.

Continue Reading

Tips to increase database performance

Here are some of the points that you should consider when you write queries:

  • Limit number of rows and columns
  • Use “Search argument able” conditions in “where” clause. They help query optimizers to use the index defined on column(s) effectively and have a higher chance of meeting index seek than index or table scan.
    “Search argument able” operators are :
    =, >, >=, <, <=, BETWEEN, LIKE (only those LIKE conditions that have a wildcard character as a suffix, eg: FirstName LIKE 'A%'
  • Do not use arithmetic operators directly on the “column name” in the “where” clause. Instead use logical workarounds to get performance benefits. eg:
    instead of

    WHERE HourlyRate * 8 <= 100

    use

    WHERE HourlyRate <= 100/8
  • Do not use functions on columns in the “where” clause. eg:
    a. Instead of

    WHERE DATEPART(YYYY,CreateDate)='2016'

    use

    WHERE CreateDate >= '01/01/2016' AND CreateDate <= '12/31/2016'
  • b. Instead of

    WHERE Left(LastName,1)='A'

    use

    WHERE LastName LIKE 'A%'
  • Use proper primary and Foreign Key constraints. They help the query optimizer select the best-suited execution plan for the query.
  • Here are some of the points that you should consider when designing indexes.

  • Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
  • Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
  • Try to create indexes on columns that have integer values rather than character values.
  • If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
  • Continue Reading

    ASP.Net session size in SQL Server session database

    Problem:
    We need to calculate ASP.Net Session size in a SQL server session database

    Solution:

    Use the following query(shamelessly stolen from asp.net: checking session size in SQL Server ASPState DB) to check the size of the session state data.

    SELECT 
        a.SessionId, 
        SUBSTRING(a.SessionId, 25, 8) AS AppIDHex, 
        b.AppId AS AppIDDec, 
        b.AppName, 
        DATALENGTH(a.SessionItemLong) AS SessionSize 
    FROM 
        dbo.ASPStateTempSessions AS a 
        LEFT OUTER JOIN 
        dbo.ASPStateTempApplications AS b 
        ON 
        SUBSTRING(a.SessionId, 25, 8) = 
        SUBSTRING(sys.fn_varbintohexstr(CONVERT(VarBinary,b.AppId)), 3, 8) 
    WHERE 
        (DATALENGTH(a.SessionItemLong) > 0) 
    ORDER BY SessionSize DESC
    
    Continue Reading