March 21, 2019 at 9:55 am
So to start with, man, I sure have a lot of weird issues. I appreciate all the help and direction I've gotten from you guys.
I'm aware that this OS wait type has to do with XP_CMDSHELL or things accessing the operating system from SQL Server. So just wanted to get that out of the way.
I have two instances on this box. One test and one stage. I implemented a tlog strategy on both. Stage finishes all tlogs in literally 30 seconds but for some reason on test I have a 5 minute delay between every database on the server with this PREEMPTIVE_OS_PIPEOPS wait. I've (obviously) tried rebooting the SQL server as well as the server itself. There are no blocking processes on SQL server itself. These tlog backups it is creating are literally only 10-30KB in size for the most part. I'm curious if anyone else has run into this in the past?
Thanks for the assistance!
March 21, 2019 at 9:56 am
Oh just to clarify, the tlogs are done by a couple stored procedures, its not a sql server maintainence task or something like that. The exact same solution is deployed both on test and stage.
EDIT: and when I say exact same solution I literally did a right click and create to on the job and just changed the folder and log paths to deploy it to stage. (sloppy but I wanted to make sure it was identical for testing purposes)
March 25, 2019 at 1:13 am
Are you using xp_cmdshell in your backup solution?
If so, what code is it running, and have you tried running that code manually outside of SQL to see if there is a performance difference outside SQL Server?
March 25, 2019 at 1:20 pm
There is a process that does use command shell however, this is not actually doing anything ATM so it shouldnt hold up the script. Its basically used to create the backup folder if its missing as well as delete any backups older than 7 days.. but we switched where we store our backups so that isn't actually doing anything. I don't really want to butcher up the script for a process that -shouldn't- be affecting anything. Also, like I said, this exact same backup solution with the same xp_cmdshell code is running just fine on another instance on the same box.
March 25, 2019 at 5:40 pm
oogibah - Monday, March 25, 2019 1:20 PMThere is a process that does use command shell however, this is not actually doing anything ATM so it shouldnt hold up the script. Its basically used to create the backup folder if its missing as well as delete any backups older than 7 days.. but we switched where we store our backups so that isn't actually doing anything. I don't really want to butcher up the script for a process that -shouldn't- be affecting anything. Also, like I said, this exact same backup solution with the same xp_cmdshell code is running just fine on another instance on the same box.
Are there any difference though in the code run on the instance that is working vs the one failing? For example, if your code used to delete backup files, it may still be performing some kind of list operation (like dir) on the old directory (unless you've explicitly excluded it in code). If there is a lot of files in that old directory, that could be causing the slowness.
This is just an example, but this is also why you should extract that code and test it outside of SQL Server to try and determine if SQL is causing slow performance or simply being impacted by slow performance of the shell command.
Also, are you using custom shell code for this, or the extended stored procedures provided by Microsoft, i.e. xp_create_subdir and xp_delete_file?
March 25, 2019 at 6:21 pm
HandyD - Monday, March 25, 2019 5:40 PMoogibah - Monday, March 25, 2019 1:20 PMThere is a process that does use command shell however, this is not actually doing anything ATM so it shouldnt hold up the script. Its basically used to create the backup folder if its missing as well as delete any backups older than 7 days.. but we switched where we store our backups so that isn't actually doing anything. I don't really want to butcher up the script for a process that -shouldn't- be affecting anything. Also, like I said, this exact same backup solution with the same xp_cmdshell code is running just fine on another instance on the same box.Are there any difference though in the code run on the instance that is working vs the one failing? For example, if your code used to delete backup files, it may still be performing some kind of list operation (like dir) on the old directory (unless you've explicitly excluded it in code). If there is a lot of files in that old directory, that could be causing the slowness.
This is just an example, but this is also why you should extract that code and test it outside of SQL Server to try and determine if SQL is causing slow performance or simply being impacted by slow performance of the shell command.
Also, are you using custom shell code for this, or the extended stored procedures provided by Microsoft, i.e. xp_create_subdir and xp_delete_file?
They are exactly the same except for the folder they go into (one goes into a stage and one goes into a test folder) .. I don't have the code in front of me to comb through it to see if it uses one of those but I'm pretty sure it uses xp_delete_file.
March 26, 2019 at 8:42 am
oogibah - Monday, March 25, 2019 6:21 PMHandyD - Monday, March 25, 2019 5:40 PMoogibah - Monday, March 25, 2019 1:20 PMThere is a process that does use command shell however, this is not actually doing anything ATM so it shouldnt hold up the script. Its basically used to create the backup folder if its missing as well as delete any backups older than 7 days.. but we switched where we store our backups so that isn't actually doing anything. I don't really want to butcher up the script for a process that -shouldn't- be affecting anything. Also, like I said, this exact same backup solution with the same xp_cmdshell code is running just fine on another instance on the same box.Are there any difference though in the code run on the instance that is working vs the one failing? For example, if your code used to delete backup files, it may still be performing some kind of list operation (like dir) on the old directory (unless you've explicitly excluded it in code). If there is a lot of files in that old directory, that could be causing the slowness.
This is just an example, but this is also why you should extract that code and test it outside of SQL Server to try and determine if SQL is causing slow performance or simply being impacted by slow performance of the shell command.
Also, are you using custom shell code for this, or the extended stored procedures provided by Microsoft, i.e. xp_create_subdir and xp_delete_file?
They are exactly the same except for the folder they go into (one goes into a stage and one goes into a test folder) .. I don't have the code in front of me to comb through it to see if it uses one of those but I'm pretty sure it uses xp_delete_file.
so I looked at it this morning and it does not.. it uses cmd del command
IF @debug = 1 PRINT 'master.dbo.xp_cmdshell ''DEL "' +@bkpfilename + '"'', no_output'
EXEC('master.dbo.xp_cmdshell ''DEL "' +@bkpfilename + '"'', no_output')
Also, the other weird thing about it, is it seems to be a consistant timing that it waits between each database.. I've watched it process and it submits the command to do the tlog.. waits for like 5 minutes then runs it and goes to the next database.. and this time seems to grow every time it runs the job.. the first time I ran this job it took 15 minutes.. then 25 minutes.. then 28 minutes.. then 45 minutes.. and the last time it ran it started at 10AM and didnt finish until 3:45PM. Insane.
March 26, 2019 at 1:24 pm
So at this point, I'm actually not sure that its this wait that is causing the issue at all.. I ran a paul randall script.. pretty lengthy so I wont post it here.. but it shows on both the test and stage the avg wait/s of the os_pipeops is about 7 seconds.. which is WAY higher than 2 of the other places I have this (one is at 4.5 but it also has redgate monitor on it) and the other one its .07 seconds.. But this doesn't account for the fact that in stage the job runs fine 12 minutes every time.
March 27, 2019 at 9:11 am
oogibah - Tuesday, March 26, 2019 1:24 PMSo at this point, I'm actually not sure that its this wait that is causing the issue at all.. I ran a paul randall script.. pretty lengthy so I wont post it here.. but it shows on both the test and stage the avg wait/s of the os_pipeops is about 7 seconds.. which is WAY higher than 2 of the other places I have this (one is at 4.5 but it also has redgate monitor on it) and the other one its .07 seconds.. But this doesn't account for the fact that in stage the job runs fine 12 minutes every time.
Okay, I found the issue.. just need to figure out why its happening just on this instance.. basically there is a query that gets the list of the backups for the delete option from backupset and backupmediafamily
.. these are then checked against the operating system to see if they exist.. code seems to be skipping the entire checking section and going straight to deleting the files(which don't exist because they have been moved)... I think I just havent seen the issue on the other boxes yet because it hasnt surpassed the delete files date.
--- EDIT * Found the error in the code * No longer an issue ;).. For some reason the person that wrote this code decided to put in if @type <> 'TLOG' then do the checks to see if they are still in the file system before trying to delete... no idea why they would do that but glad I combed through it and found it..
I did end up writing up a new solution for populating the table to compare against the system tables to make sure the files exist before attempting to delete themDECLARE @bkpfilename NVARCHAR(1024)
DECLARE @Bkpfilename2 NVARCHAR(1024)
DECLARE @BACKUPDIR nvarchar(1024)
DECLARE @dbname sysname
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BACKUPDIR OUTPUT;
-- this will be replaced with a cursor in the final solution
SELECT TOP(1) @DBname = name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'model', 'msdb') ORDER BY name
SELECT @BACKUPDIR= @BACKUPDIR+'\'+@DBname+'\'
SELECT @bkpfilename = @backupdir +'*.bak'
SELECT @Bkpfilename2= @BACKUPDIR + '*.trn'
CREATE TABLE #filelist (fileinfo VARCHAR(1024) NULL)
INSERT INTO #filelist EXEC('master.dbo.xp_cmdshell @command = ''DIR ' +@bkpfilename+ ' '+@bkpfilename2+'/b /a-d''')
SELECT * FROM #filelist
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply