Archive

Posts Tagged ‘database’

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

;

Changing SQL database to single user mode and back to Multi User mode

October 26, 2010 1 comment

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users.

It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode.

ALTER  DATABASE <<Database Name>>  SET SINGLE_USER  WITH NO_WAIT

The NO_WAIT clause will set it to single user mode as soon as you execute the query.  An alternate to this is by using the system stored procedure sp_dboption

Continue Reading ….

 

 

 

Remove Not Null Constraint on a column using Alter Table statement

October 8, 2010 3 comments

Many times , it may be necessary to remove Not Null constraint used for a column in  a table. It is usually necessary to overcome unexpected integrity constraints ( rarely occurs, if designed well) against a table. We can remove the Not Null constaint by using  ALTER TABLE …. ALTER COLUMN … option.

A sample query to is as below

Continue Reading …

SQL Interview Questions – Basics 2

October 8, 2010 Leave a comment

What are the advantages and disadvantages of Surrogate Key ?

Pros:

  1. Business Logic is not in the keys.
  2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
  3. Joins are very fast.
  4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

  1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
  2. Cannot be used as a search key.
  3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
  4. Always requires a join when browsing the child table(s).

 

What are  identity columns?

An Identity column is a column (field ) in a database table that is made up of values generated by the database.

Continue Reading …