Creating .csv or .txt file using batch (.bat) commands

June 27, 2018 Leave a comment

Copy and paste the below code into a batch file (.bat file) to create a .csv or text file from the resultset of query. You can either use the query directly in the batch file or use another file with SQL query as the source. In this case, I have used file C:\SQLCMDTEST\TestCode.sql  as the query source. you can point this to any file that have a SQL query

create the file

sqlcmd -S “SQLServerName” -d “YourDBName” -i “C:\SQLCMDTEST\TestCode.sql” -W -s “,” -o “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

delete the second row in the file with —————,—————  value

findstr /V “\——-” “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv” > “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT_06222018.csv”

 

@@echo off

del errors /f /s /q

rd Errors

md Errors

sqlcmd -S “SQLServerName” -d “YourDBName” -i “C:\SQLCMDTEST\TestCode.sql” -W -s “,” -o “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

findstr /V “\——-” “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv” > “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT_06222018.csv”

del “\\sqlftps\FTPFiles\IMLogs\IM_INSIGHT.csv”

 

Advertisements

Transact SQL code to identify queries with highest I/O in SQL server

June 22, 2018 Leave a comment

Use the below transact SQL code to identify the queries / procs consuming most I/O. You can combine this with the execution plan of the query to derive insights on the changes required to improve the query performance

 

SELECT TOP 100
TOTAL_LOGICAL_READS
, TOTAL_LOGICAL_WRITES
, S.EXECUTION_COUNT
,( TOTAL_LOGICAL_WRITES + TOTAL_LOGICAL_READS ) AS TOTAL_IO
,( TOTAL_LOGICAL_WRITES + TOTAL_LOGICAL_READS ) / S.EXECUTION_COUNT AS AVERAGE_IO
,T.TEXT AS QUERY_TEXT
,OBJECT_NAME(T.OBJECTID)
,DB_NAME(T.DBID) AS DATABASE_NAME
FROM SYS.DM_EXEC_QUERY_STATS S
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) AS T
WHERE T.DBID = DB_ID()
ORDER BY TOTAL_IO DESC, AVERAGE_IO DESC;

 

Above code in combination with Transact SQL code to identify longest running queries in SQL server can be used as first steps to identify the issues in your query performance.

 

Transact SQL code to identify longest running queries in SQL server

June 22, 2018 1 comment

Provided below is a short and quick script to get the longest running queries and the query plan from SQL server for any specific database. Remove the Where clause in the code, if you want to check for all Databases. you can check execution plans of  queries to see what inefficiencies are causing the queries to run longer and possible improvement to queries like indexes or  rewrite a query differently.

 

USE MyDB;

GO;

SELECT TOP 100
S.PLAN_HANDLE
,A.SQL_HANDLE
,T.TEXT AS QUERY_TEXT
,S.EXECUTION_COUNT
,S.MAX_ELAPSED_TIME
,COALESCE(S.TOTAL_ELAPSED_TIME / S.EXECUTION_COUNT, 0) AS AVERAGE_ELAPSED_TIME
,S.CREATION_TIME
,S.LAST_EXECUTION_TIME
,P.QUERY_PLAN
FROM SYS.DM_EXEC_QUERY_STATS S
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(S.PLAN_HANDLE) AS P
CROSS APPLY SYS.DM_EXEC_SQL_TEXT( S.SQL_HANDLE ) T
WHERE T.DBID = DB_ID()
ORDER BY
MAX_ELAPSED_TIME DESC

Transact SQL ( T-SQL) to get all indexes in a database of SQL Server

June 22, 2018 Leave a comment

Below query can be used to get all indexes including heaps from an SQL server database. “IndexKeyOrder” specifies the order of the column within the index.

USE MyDatabase;

GO;

SELECT Coalesce(IX.name, ) AS IndexName

, OBJ.name AS ObjectName

,SCHEMA_NAME(OBJ.schema_id) AS SchemaName

, OBJ.type AS ObjectType –U= TABLES , V= VIEWS

, CASE WHEN OBJ.is_ms_shipped = 0x0 THEN 1 ELSE 0 END AS SystemObjects

, IX.index_id AS IndexID —- 1= CLUSTERED , 0 = HEAP, 2+ = NONCLUSTERED

, IX.type_desc as IndexType

, COL.name AS ColumnName

, IX_COL.is_included_column AS IsIncludedColumn

, IX_COL.key_ordinal AS IndexKeyOrder — 0 is for included columns

, IX.is_disabled AS IsDisabledFlag

FROM

sys.indexes AS IX

INNER JOIN sys.objects AS OBJ

ON IX.object_id = OBJ.object_id

AND OBJ.type IN (‘U’,‘V’) –U= TABLES , V= VIEWS

LEFT OUTER JOIN sys.index_columns AS IX_COL

ON IX.object_id = IX_COL.object_id

AND IX.index_id = IX_COL.index_id

LEFT OUTER JOIN sys.columns AS COL

ON IX_COL.object_id = COL.object_id

AND IX_COL.column_id = COL.column_id

ORDER BY SchemaName

, ObjectName

, IndexName

, IndexKeyOrder

;

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

SSIS : Using and Configuring For Loop Container

July 5, 2014 Leave a comment

What is For Loop Container?

For loop defines a repeating control flow. It has similar behavior as the “FOR” loop available in programming languages.  The For Loop container uses 3 expressions as definition

  1. Initialization expression:  it assigns value to the loop counters. This expression is optional.
  2. Evaluation expression : It contains the expression to test whether the loop should continue executing  or exit.
  3. An optional iteration expression that increments or decrements the loop counter.

The expressions used must be valid SSIS expression. A variable is generally used in the expression.

Using and Configuring For Loop Container

(Task in hand: print counter variable value for each iteration times.)

Drag a for loop container into the SSIS Control flow window from the Toolbox. Double click on the for loop container to view the “For Loop Editor”. As mentioned earlier it contains 3 expressions. Now create a variable by name “TestVariable” of type “int32”.

Use this variable as counter in the container using expressions as shown below.

Read more…

 

 

Advantages and differences between CHAR and VARCHAR datatypes in SQL Server

April 25, 2014 1 comment

Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages between these two?

The obvious difference that comes to our mind is related to the length. Yes! You are right. The varchar supports variable length and Char supports fixed length. This is the main advantage varchar datatype. for example,  let us consider varchar(10) and char(10) . To store “GEEKEPISODE”, both the datatypes will use the complete length allocated and to store “GEEK”, CHAR datatype will use the complete 10 characters  allocated, but VARCHAR will use only 4.

From the above examples it might seem that VARCHAR is better than CHAR all the time. But remember, CHAR was included as a separate datatype because it has certain advantages over VARCHAR.