How to prevent lock escalation

There are several ways to reduce the lock footprint og expensive queries so that the lock escalation thresholds are not exceeded.

Beneath I have described how I do it most of the times. Other ways can be read in this article: How to resolve blocking problems that are caused by lock escalation in SQL Server


SET ROWCOUNT 500
delete_more:
DELETE FROM BATCHHISTORY WHERE CREATEDDATETIME < '2015-11-02' OPTION(MAXDOP 1)

IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
GO

Udgivet i Dynamics Ax, SQL Server - scripts | Kommentarer lukket til How to prevent lock escalation

Dynamics Ax 2012 AdminMode

When you are working with Ax security is Ax ../4.0/2009 you have tried a bunch of times to login as a test user to see if the security is how it should be.

In Dynamics Ax 2012 we have got a way to disable the adminmode for the user you are using, then you can test the security without logout and login as.


static void SEOSOFT_SysAdminModeDisable(Args _args)
{
SecurityUtil::sysAdminMode(false);
}

static void SEOSOFT_SysAdminModeEnable(Args _args)
{
SecurityUtil::sysAdminMode(true);
}

When you have toggled the ad

Udgivet i Dynamics Ax | Kommentarer lukket til Dynamics Ax 2012 AdminMode

How to move an object from one schema to another schema


If you have created a view|Procedure|Table in the wrong schema in SQL Server, you need to run this script.

ALTER SCHEMA [NewSchemaName] TRANSFER [OLDSCHEMA].[ObjectName]

If you have created more than one objects in the wrong schema, you can join the system table named sys.Objects, and sys.Schemas, like this.

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Udgivet i SQL Server - scripts | Kommentarer lukket til How to move an object from one schema to another schema

How to retrieve the active queries from An SQL Server

If you are wondering what the SQL server is doing, try to fire this statement, it shows the following.

– SPID – The system process ID.
– Status – The status of the process (e.g., RUNNABLE, RUNNING, SLEEPING).
– Login – Login name of the user.
– HostName – Machine name of the user.
– Blocked By – If the process is getting blocked, this value is the SPID of the blocking process.
– DBName – Name of database the process is using.
– Command – The command currently being executed (e.g., SELECT, INSERT)
– CPUTime – Total CPU time the process has taken
– DiskIO – Total amount of disk reads for the process.
– Last batch – Last time a client called a procedure or executed a query.
– Program name – Application that has initiated the connection (e.g., Microsoft Dynamics Ax, Visual Basic, MS SQL Query Analyzer)
– SPID 2 – The process ID repeated without the ECID for easy reading.
– Request
– Type – (e.g., CURSOR, NOT CURSOR, EXEC sp_execute)
– SQL Text – The SQL Text including parameters etc. If Cursor this is holding the SQL Statement behind the cursor, If sp_execute (I have a little trouble with this one :-))
– SQLPlan – Is holding the SQL plan which the statement is fired

If you find some errors or inputs etc, please mail it to seo@seosoft.dk.

DECLARE @dbName AS NVARCHAR(30)
SET @dbName = 'AX2009_PROD'
DECLARE @SPWHO2 TABLE (SPID INT, [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT NULL, Request VARCHAR(1000) NULL)
INSERT INTO @SPWHO2
EXEC sp_who2 'Active'
SELECT
a.*
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN 'CURSOR'
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN 'EXEC sp_execute'
ELSE 'NOT CURSOR'
END
) AS [TYPE]
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN
(
SELECT TOP 1 t.text
FROM sys.dm_exec_cursors (a.SPID) d
CROSS APPLY sys.dm_exec_sql_text (d.sql_handle) t
)
ELSE
(
CASE
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN
(
SELECT TOP 1 f.TEXT
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.plan_handle) f
WHERE e.session_id = a.SPID
AND f.TEXT <> ''
)
ELSE
c.text
END
)
END
) AS SQLTEXT
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN
(
SELECT TOP 1 d.sql_handle
FROM sys.dm_exec_cursors (a.SPID) d
)
ELSE
(
CASE
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN
(
SELECT TOP 1 e.sql_handle
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.plan_handle) f
WHERE e.session_id = a.SPID
AND f.TEXT <> ''
)
END
)
END
) AS SQLHANDLE,
CAST(TQP.query_plan AS XML)
FROM
@SPWHO2 AS a
,sys.dm_exec_connections b
INNER JOIN sys.dm_exec_requests AS R
ON b.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text (b.most_recent_sql_handle) c
CROSS APPLY sys.dm_exec_text_query_plan
(
R.plan_handle
, R.statement_start_offset
, R.statement_end_offset
) AS TQP
WHERE
DBName = @dbName
AND a.SPID = b.session_id
--AND a.SPID =
ORDER BY
-- a.CPUTime DESC
a.DiskIO DESC

Udgivet i SQL Server - scripts | Tagget , , , , | Kommentarer lukket til How to retrieve the active queries from An SQL Server

How to extract files from SQL Server blob fields into files.

— Write all database files (pdf) to file.
——— ——— ——— ——— ——— ——— ———
DECLARE CURSOR_Id CURSOR FOR (SELECT [RecId] FROM [dbo].[SYSOUTGOINGEMAILDATA])

DECLARE @RecId BIGINT;

OPEN CURSOR_Id

FETCH NEXT FROM CURSOR_Id INTO @RecId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @Data varbinary(max);
SELECT @Data = (SELECT convert(varbinary(max), Data, 1) FROM [dbo].[SYSOUTGOINGEMAILDATA] WHERE RecId = @RecId);

DECLARE @Path nvarchar(1024);
SELECT @Path = ‘C:\MyPDFs\Output’;

DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT [Filename] + [FILEEXTENSION] AS [PDFFile] FROM SYSOUTGOINGEMAILDATA WHERE RecId = @RecId);

DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + ‘\’ + @Filename;

DECLARE @ObjectToken INT
EXEC sp_OACreate ‘ADODB.Stream’, @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, ‘Type’, 1;
EXEC sp_OAMethod @ObjectToken, ‘Open’;
EXEC sp_OAMethod @ObjectToken, ‘Write’, NULL, @Data;
EXEC sp_OAMethod @ObjectToken, ‘SaveToFile’, NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, ‘Close’;
EXEC sp_OADestroy @ObjectToken;

FETCH NEXT FROM CURSOR_Id INTO @RecId
END
CLOSE CURSOR_Id
DEALLOCATE CURSOR_Id

— Make sure the following statement is executed to enable file IO
— From http://msdn.microsoft.com/en-us/library/ms191188.aspx
——— ——— ——— ——— ——— ——— ———
/*
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
*/

Udgivet i SQL Server - scripts | Tagget , , , | Kommentarer lukket til How to extract files from SQL Server blob fields into files.

Search for a text inside all the stored procedures in a database.

SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like ‘%%’;

Udgivet i SQL Server - scripts | Kommentarer lukket til Search for a text inside all the stored procedures in a database.

Troubleshooting the MOSS error: “An unexpected error has occurred”

Change the following values in your web.config file so that you can see what the actual error is:

 

  • Change CallStack=”false” to CallStack=”true”
  • Change <customErrors mode=”On”/>  to <customErrors mode=”Off“/> 
  • Change <compilation batch=”false” debug=”false”> to <compilation batch=”false” debug=”true”> 

When you are done, make sure to change these three back to its original value as there is a performance hit when these are turned on/enabled.

Udgivet i SharePoint 2010 Tips & Tricks | Kommentarer lukket til Troubleshooting the MOSS error: “An unexpected error has occurred”

Create statistics


-- Create the statistics for all tables and columns in the database that don’t
-- already exist. This is much easier than using CREATE STATISTICS on each
-- table, as you can only do 16 columns at a time, and have to name them each.
RAISERROR('Creating statistics on all tables and columns that are missing them', 10, 1) WITH NOWAIT, LOG
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

-- Set the automatic UPDATE STATISTICS setting to ‘ON’ for all indexes and
-- statistics for all tables and indexed views in the database.
RAISERROR('Running sp_autostats for all tables…', 10, 1) WITH NOWAIT, LOG
EXEC sp_MSforeachtable ' PRINT "?" EXEC sp_autostats "?", @flagc = "ON" '

-- Display the new names of the column indexes.
SELECT OBJECT_NAME(s.object_id) AS 'TableName', s.name AS 'StatsName'
FROM sys.stats s
JOIN sys.tables t
ON s.object_id = t.object_id
WHERE s.object_id > 100
AND s.name NOT IN
(SELECT name FROM sys.indexes WHERE object_id = s.object_id)

Udgivet i SQL Server - scripts | Kommentarer lukket til Create statistics

Finding missing indexes in SQL Server 2012

This is a nice query using sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_groups to find out which indexes is missing.


SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer],
db.[database_id] AS [DatabaseID],
db.[name] AS [DatabaseName],
id.[object_id] AS [ObjectID],
id.[statement] AS [FullyQualifiedObjectName],
id.[equality_columns] AS [EqualityColumns],
id.[inequality_columns] AS [InEqualityColumns],
id.[included_columns] AS [IncludedColumns],
gs.[unique_compiles] AS [UniqueCompiles],
gs.[user_seeks] AS [UserSeeks],
gs.[user_scans] AS [UserScans],
gs.[last_user_seek] AS [LastUserSeekTime],
gs.[last_user_scan] AS [LastUserScanTime],
gs.[avg_total_user_cost] AS [AvgTotalUserCost],
gs.[avg_user_impact] AS [AvgUserImpact],
gs.[system_seeks] AS [SystemSeeks],
gs.[system_scans] AS [SystemScans],
gs.[last_system_seek] AS [LastSystemSeekTime],
gs.[last_system_scan] AS [LastSystemScanTime],
gs.[avg_total_system_cost] AS [AvgTotalSystemCost],
gs.[avg_system_impact] AS [AvgSystemImpact],
gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage],
'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '')
+ CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_'
+ LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '')
+ CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex],
CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

Udgivet i SQL Server - scripts | Kommentarer lukket til Finding missing indexes in SQL Server 2012

Rebuild indexes ONLINE = OFF, and update statistics.

Do you have a maintenanceplan that rebuilds the indexes during the weekend, but monday morning all clients complaints that the system is saying “Not responding”. When you look at the database you can see that the Rebuild indexes hasn’t finished because some process was blocking.

If you are using the “Execute T-SQL Statement Task” then you can setup the plan to send you an email if the job isn’t finished after x seconds.

The script below rebuild all indexes in all userdatabases and update statistics.


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''

  -- create table cursor
  EXEC (@cmd)
  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @Table
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
    BEGIN
      -- SQL 2005 or higher command
      SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON )'
      EXEC (@cmd)

      SET @cmd = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
      EXEC (@cmd)
    END
    ELSE
    BEGIN
     -- SQL 2000 command
     DBCC DBREINDEX(@Table,' ',@fillfactor)
    END

    FETCH NEXT FROM TableCursor INTO @Table
  END

  CLOSE TableCursor
  DEALLOCATE TableCursor

  FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Udgivet i SQL Server - scripts | Kommentarer lukket til Rebuild indexes ONLINE = OFF, and update statistics.