The code below will give you the top 10 most executed stored procedures in your database, sorted by execution count.
Interested to see how long your SQL server agent is up and running, or you want to see if that agent is operational? You can use the t-sql code below to find out:
The SQL statement below will give you the top 100 longest running SQL statements in your database, sorted by duration.
Looking for a table, but you don’t remember in which database it’s located? The code below will create a stored procedure, which you can use in order to find a table in multiple databases.
You can use the t-sql code below in order display the size of each table in a specific database.
Sometimes you need to do maintenance on a database, and you don’t really want to use single user mode or disable accounts. In that situation you could kill all connections to a specific database.In order to achieve that, you can use the t-sql code below.
You can create a temporary lock in T-SQL for -for example- testing:
from : http://jasonbrimhall.info/2014/01/02/day-9-queries-going-boom/ The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more
The following t-sql statement returns the server name, server instance, instance, edition & version of the running SQL-Server. SELECT SERVERPROPERTY(‘MachineName’) AS [ServerName], SERVERPROPERTY(‘ServerName’) AS [ServerInstanceName], SERVERPROPERTY(‘InstanceName’) AS [Instance], SERVERPROPERTY(‘Edition’) AS [Edition], SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion], Left(@@Version, Charindex(‘-‘, @@version) – 2) As VersionName
When tracing with SQL profiler, it returned a query using ‘sp_cursorprepexec’.When executing the same query within SSMS, it failed with: sp_cursorprepexec: The value of the parameter XX is invalid. I managed to solve this by adding ‘+ 0x1000’ to the parameter