EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” out “file path” -E -n -C RAW -S ab1-srv-ab02 -T’
EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” in “file path” -E -n -C RAW -b 10000 -a 16384 -q -S ab1-srv-ab02 -T’
EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” out “file path” -E -n -C RAW -S ab1-srv-ab02 -T’
EXEC xp_cmdshell ‘bcp.exe “dbase.schema.table” in “file path” -E -n -C RAW -b 10000 -a 16384 -q -S ab1-srv-ab02 -T’
Tuning a piece of code today I managed to get the execution time down from 32 minutes to 2 minutes by replacing a format function with a convert function. Format is a clr function and doesnt perform well against large datasets.
Problem: need to create an object that can be used in a select from statement but due to limitations such as not being allowed to use variables in views or use side effecting operators in functions no object can be easily created.
Solution:
Create a stored procedure which returns the required dataset
Ensure a self referencing linked server exists on the server
Create view as a select * from openquery(SERVER,’exec stored_proc_name’)
in some cases you may need to modify the openquery statement
if your proc uses dml use the nocount on clause
if your proc uses temp table use the set fmtonly on clause
eg: Create view as a select * from openquery(SERVER,’SET NOCOUNT ON;SET FMTONLY OFF;exec stored_proc_name’)
EXEC master.dbo.xp_sqlagent_enum_jobs 1,’xxx’ will output a table where running = 1 indicates a job is running
the following will return a list of running jobs
SELECT b.name
FROM msdb.dbo.sysjobactivity a
INNER JOIN msdb.dbo.sysjobs b ON a.job_id=b.job_id
WHERE session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
AND start_execution_date IS NOT NULL AND stop_execution_date IS NULL
View code, search for TablixRowHierarchy
replace {TablixMember /}
with
{TablixMember}
{Group Name=”Details” /}
{TablixMembers}
{TablixMember /}
{/TablixMembers}
{/TablixMember}
and switch back to your report, make sure to replace curly brackets with less than / greater than symbols (which are not allowed by wordpress on this page)
event: xml_deadlock_report
output the data to a file so that when you view target data you will be presented a deadlock graph which give you the visual deadlock graph, this isnt visible if you output to ring buffer