SET STATISTICS IO ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber

FROM dbo.EmployeeReports er

WHERE er.ReportNumber LIKE '%33%'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

check table partition

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%TABLE1%'

Check status of index fragmentation

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = 29 and dbtables.[name] like '%BaseStore%'

Short Script to Drop foreign keys

eclare @sql varchar(max) = (
    select 
        'alter table ' + quotename(schema_name(schema_id)) + '.' +
        quotename(object_name(parent_object_id)) +
        ' drop constraint '+quotename(name) + ';'
    from sys.foreign_keys
    for xml path('')
);
exec sp_executesql @sql;

Find forign keys in the database

SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

sp_MSforeachtable

For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

That's a whole lot more palatable than developing a cursor-based solution!

Here's another example. The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';

So, the next time you need to loop through each table, give the sp_MSforeachtable procedure a try.

Cheers!

Display concurrent connection in SQLServer

SELECT DB_NAME(dbid) AS DBName,COUNT(dbid) AS NumberOfConnections,loginameFROM    sys.sysprocessesGROUP BY dbid, loginameORDER BY DB_NAME(dbid)

Grant views only permssions to all the objects

declare @login VARCHAR(30) = 'rdc\rxxxx'

SELECT 'Grant View Definition ON ' + schema_name(schema_id) + '.' + [name] + ' TO ' + '[' + REPLACE(REPLACE (@login, '[', ''), ']', '') + ']'
FROM sys.all_objects s
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
/*
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/
AND is_ms_shipped = 0
ORDER BY s.type, s.name 

Index fragmentation query

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the 'where' clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.
For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient.

The following table summarizes when to use each one:

Reference Values (in %)	Action	SQL statement
avg_fragmentation_in_percent > 5 AND < 30	Reorganize Index	ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30	Rebuild Index	ALTER INDEX REBUILD

NEW ID

If you a have a column of type ‘uniqueidentifier’ in your SQL table, and you want to auto generate a new Guid every time when you insert a record, you can use the ‘Default Value or Binding’ property of ‘Column Properties’.

Set Default Value or Binding = newid()

GRANT ,INSERT AND UPDATE TO USER

SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type IN (N'U', N'V');

SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

GRANT CONTROL on objects in SQLserver

SELECT 'GRANT CONTROL  ON [' + SCHEMA_NAME(schema_id) + '].[' + [name] + '] TO [access];'
FROM sys.objects
WHERE type = 'FN' OR TYPE = 'P' OR TYPE='V'

GRANT VIEW SERVER STATE

use master
go
GRANT VIEW SERVER STATE TO <user_name>

sp_change_users_login

EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

database principal owns a schema in the database, and cannot be dropped

To find  principal own schema run below command

SELECT s.nameFROM sys.schemas sWHERE s.principal_id = USER_ID('pinaladmin');

then run this command 

ALTER AUTHORIZATION ON SCHEMA::db_owner  TO dbo

SQL : ASYNC Network IO

ASYNC Network IO
The “async network io” (in SQL 2005/2008) and “networkio” (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough. This will result in filling the network buffers so that SQL Server cannot send more data to the client. Therefore, the process executing the batch will need to wait for the ability to continue sending results to the client.
Reducing Waits / Wait times:
If there are significant wait times on “async network io’, review the client applications. Most often, client applications will process rows one at a time using fetches. This may cause the server process to wait on “async network io” when serving up many rows. If this is the issue, there is nothing you can do to improve the SQL Server process performance. Instead, the client application (or middle tier if a web application) may need to be modified to allow for more efficient retrieval of data. Review the following list for client application issues:
• Some applications, such as Microsoft Access, will ask for large result sets (typically identified by select statements with no where clause or full table scans), and then further filter the data on the client. If this is causing significant wait time, see if it’s possible to create a view for the client application to use instead. This will significantly reduce the amount of data being pushed to client since all of the filtering will done on SQL Server. Another fix could be to add a ‘where clause’ or further restrict the query so that less data is being sent to the client.
• Identify large result sets and verify with the application or developer team how the data is being consumed. If the application is querying large result sets but using only a few rows, consider only querying the rows that are needed or use ‘TOP n’ to reduce the number of rows returned.
• If you are encountering high “async network io” wait times when performing data loads on the server, make sure the shared memory protocol is enabled for the SQL Server instance and the session is connected using net_transport = ‘Shared memory’. You can determine the net_transport for the connection by looking at the DMV – sys.dm_exec_connections.
If the above tuning tips are reviewed and applied, but the server is still is encountering high “async network io” times, then ensure there aren’t any network related issues:
• Review counters such as ‘Batch Requests per second’. Any values over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on the Network Interface with values over 0.6 are getting excessive.
• Check network adapter bandwidth - 1 Gigabit is better than 100 megabits and 100 megabits is better than 10 megabits.
• Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if autodetect is picking the fastest speed.
• Validate all of the network components between the client application and the SQL Server instance (e.g. switches / routers).



Conclusion
When a session waits on the "async network io” event, it may be encountering network issues. More likely, however, it may be an issue with the client application not processing the data quickly enough. If the wait times for “async network io” are high, review the client application to see if large results sets are being sent to the client. If they are, work with the developers to understand if all the data is needed and reduce the size of result set if possible. Ensure that any data filtering is performed in SQL Server instead of the client by utilizing views or more specific where clauses. Use the ‘TOP n’ clause so that only the rows that the client needs are returned to the client. Investigate network issues, if client application tuning tips do not apply.

Seach object in SQLServer database

Select [name] as ObjectName, Type as ObjectType
From Sys.Objects
Where 1=1
and [Name] like '%List%'

grant permssion to user defined types

GRANT EXECUTE ON TYPE::SysObjectsCount to TestLogin

GRANT VIEW SERVER STATE

use master
go
GRANT VIEW SERVER STATE TO <user_name>

SQL Locks

SQL Server has more than 20 different lock types . let us focus on common locks

• Shared locks (S). Those locks acquired by readers during read operations such as SELECT. I’d like to mention that it happens in most part of the cases but not all the time. There are some cases when readers don’t acquire (S) locks. We will talk about it later.
• Exclusive locks (X). Those locks acquired by writers during data modification operators such as Insert, Update or Delete. Those locks prevent one object to be modified by the different sessions. Those locks are always acquired and held till end of transaction
• Update locks (U). Those locks are the mix between shared and exclusive locks. SQL Server uses them with data modification statements while searching for the rows need to be modified. For example, if you issue the statement like: “update MyTable set Column1 = 0 where Column1 is null” SQL Server acquires update lock for every row it processes while searching for Column1 is null. When eligible row found, SQL Server converts (U) lock to (X).
• Intent locks (IS, IX, IU, etc). Those locks indicate locks on the child objects. For example, if row has (X) lock, it would introduce (IX) locks on page, table and database level. Main purpose of those locks is optimization. This about situation when you need to have exclusive access to the database (i.e. (X) lock on database level). If SQL Server did not have intent locks, it would have to scan all rows in the all objects and see if there are any low level locks acquired.

how to find Max degree of paralleism

SELECT value_in_use
  FROM sys.configurations
  WHERE name = 'max degree of parallelism'

Can't view stored procedure

select 'GRANT VIEW DEFINITION ON ' + quotename(specific_schema) 
+ '.' + quotename(specific_name)
+ ' TO ' + 'BOB'
  from INFORMATION_SCHEMA.routines
where routine_type = 'PROCEDURE'

How to script all SQLAgent jobs

Click the 'Jobs' folder under the SQL Server Agent node in SSMS, then hit the F7 key (brings up the Summary pane).  Highlight all the jobs you want to script using a combination of Shift and Ctrl keys, then right click, 

RESTORE DATABASE [DataBase Name] WITH RECOVERY

RESTORE DATABASE [DataBase Name] WITH RECOVERY

Trasaction replication vs High Availability

The Transactional Replication (TR) is still one of my favourite parts of SQL Server, and provides some distinctive advantages over Always On Availability Group (AG) Read Only secondaries. The TR advantages include :-

1. You don’t have to replicate everything when using TR, you can select tables, columns or even data subsets. You can also eliminate triggers, constraints and other definitions that add no value to a reporting implementation.
   (Always On Availability Groups mirrors everything in the database)
2. TR sends deltas and therefore minimises data transfer traffic just like AG but TR has the flexibility of continuous or scheduled data transfers. AG mirroring has only continuous data transfer.
3. TR allows you to move the reporting workload off to another server just like AG but
   • TR is available in SQL Server Standard Edition
     (AG is only available in the Enterprise Edition)
   • You can replicate to lower or higher edition/version of SQL Server instances
     (AG secondary servers can only be Enterprise Edition and same version)
4. When using TR, you can add reporting specific indexes directly to the replicated (slave) database. With AGs you have to add all indexes to the Primary database.
5. When using TR, you can add extra reporting objects (eg Summary tables, stored procedures, views, functions) directly to the replicated database.
6. When using TR, you can run maintenance (index rebuilds) on the primary server independently of the replicated database. I.e. It doesn’t send a mass of transaction log changes to the replicated database. In addition, when using TR, you can run index rebuilds on the replicated database with impacting the Primary server.
7. When using TR, you can configure database security on the replicated database independently of the primary database.

Grant View Definition

select 'GRANT VIEW DEFINITION ON [' + schema_name(schema_id) + '].[' + name +
       '] TO ' + '[RDC\shunt]'
  from sys.all_objects
 where type_desc = 'SQL_STORED_PROCEDURE'
   and schema_id <> schema_id('sys')

ASYNC_NETWORK_IO

What is ASYNC_NETWORK_IO  or  NETWORK_IO ?

When a query is fired, SQL Server produces the the results ,place it in output buffer and send it to client/Application. 

Client/Application then fetch the result from the Output buffer, process data  and send acknowledgement to SQL Server. If client/Application takes long time to send acknowledgement then SQL Server waits on ASYNC_NETWORK_IO (SQL 2005/2008) or  Network_IO (SQL 2000) before it produces additional results.

Impact of this wait type

1. SQL Server will not release the locks held by the query unless Acknowledgement is received from Client. Which might cause blocking.

2. Query duration increases so the query will be slow.

How to troubleshoot ASYNC_NETWORK_IO  or  NETWORK_IO   wait type?

Limit the result set: Limit the number of rows you fetch from SQL Server. Many application designers fetch the data from whole table with out filtering (Where clause) and do the filtering on client side. This is very wrong approach  there has to be filtering on server side.
If there is need to fetch huge number of rows from server and if other sessions are experiencing blocking because of ASYNC_NETWORK_IO  or  NETWORK_IO  Then  insert all the rows in to temp table and do the select from temp table.   

Check the performance of client application:  Check if client / Application system is experiencing system resource bottleneck. Application will not be able to process the result set faster if there system resource bottleneck

Network: Check the network speed between SQL Server and  Client/Application system. If the network is slow application can not fetch result faster.

No lock hint or Snapshot isolation level: Check if you can use No lock hint or Snapshot isolation level