Category Archives: Third Party Tools

SQL Server: Idera SQL Doctor

If you don’t know much about SQL Server internals and its performance tuning techniques, but still need your SQL Server performance, right upto the mark, then you must try SQL Doctor, a really helpful tool by Idera. 
SQL doctor is a revolutionary technology that analyzes the performance of SQL Server and provides recommendations for improving performance.
Advertisements

SQL Server: How to Import Data from Pervasive

To extract data frompervasive data files Pervasive database Engine is required. Latest Trialversion 11 can be downloaded from http://www.pervasivedb.com/psqlv11/pages/default.aspx
Once you have installedpervasive Engine, follow following steps to view and import it into SQL Server.
1. Open Control Center through programs>Pervasive> PSQL v11>control Center and documentation.

2.      Make sure services pervasive PSQLRelational Engine and pervasive PSQL Transaction Engineare running (Expand services in Left panel)
3.      To open a database Expand Engines>[server name] > Databases
4.      Right Click on Databases and selectNew>Database
5.      In Database Name Box give a name toyour database.
6.      In Location Box locate the databasefiles path.  (.mkd, .ddf, .dat)
7.      You can uncheck “relationalintegrity enforced”.
8.      Click Finish to complete the process.

 9.    Expand Database and you can see theadded database.
10.   Double click on a table to see records
 

Moving data into SQLServer:
1.      Right click on target database andselect Tasks> import data to open import and export wizard.
2.      Hit next on the welcome screen.

4.      To choose a data source click on thedrop down list and select Pervasive Provider, Release 3.2
5.      Under Standard connection: specifypervasive Database Name, Host and server DSN as shownin figure below.  DSN DEMODATA is automatically created byPervasive Engine.
6.      Click next.
7.      Chose SQL server native Client asDestination. Hit Next.
8.       Click next
9.     Provide Query to extract data from agiven table. Click parse to verify. Click next
10.   Change Destination table name and click on EditMappings button.  You can also preview source data.
11.   In column mappings you can specify Field names,data type and size for destination table.
12.   Click “OK” button to close Column mappings and hit finish toexecute the import process.
13.   On successful completion refresh database in SQLServer to see imported data.

>SQL Server: What I Have Learned in May 2011

Dedicated to Most Energetic SQL Expert Gail Shaw

SQL Tips & Tricks:
Third Party Tools:
Performance Tuning:
SQL Server Management Studio:
Database Management Views & Functions:

SQL Server: Shortcuts for TSQL Code in SSMS

For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.
If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.

It has shortcuts like:

                                    SSC        =       SELECT  COUNT(*) FROM
                                    SSF         =       SELECT * FROM
                                    UPD       =       UPDATE <>
 SET    <>
 FROM   <>

>SQL Server: Keeping Log of Each Query Executed Through SSMS

>

During a normal working day, a DBA or Developer executes countless queries using SQL Server Management Studio. Some of these queries, which are thought important, are saved and roughly 80% of query windows are closed without pressing save button. But after few minutes, hours or even days, most DBAs and Developers like me want their quires back, which they have executed but can’t save.
SQL Server Management Studio has no such feature through which we can get our unsaved queries back. If you need to keep log of each query you have executed in SSMS then you must install free tool SSMS Tools Pack 1.9” by Mladen Prajdić, which contains “Query Execution History” and much more. 

>SQL Server: How to Find Costliest Nodes in Graphical Execution Plan

>

Graphical Execution Plan is first place, a DBA like to visit, during query optimization. If execution plan contains few nodes then it is easy to find out the culprit node. But what if target query is complex and resultant execution plan contains dozens of nodes. That’s, what happened this morning to one of my colleague, who was trying to optimize a complex query but execution plan was showing near 100 nodes.
Now finding out expensive nodes for such execution plans is also a challenge. This problem of finding costliest nodes, can easily be solved by getting execution plan XML and executing it with a query as explained by Mladen Prajdić 
But, I think most beautiful way to solve this problem is SQL Sentry Free Tool “Plan Explorer”. You can download it free from follow link.
For example consider following query
USE AdventureWorks
GO
SELECT  *
FROM    Sales.vSalesPerson
 

Here is graphical execution plan of above query, produced by SQL Server Management Studio
And following is the execution plan generated by SQL Sentry Plan Explorer. Costliest nodes are already marked as red and orange as per their cost. So in just one look, one can easily find out these costliest nodes.