SQL Server Logshipping between WORKGROUP Machines
This article describes the requirements to log ship databases between workgroup machines
Many companies rely on scalable SQL queries that deliver maximum throughput for the lowest cost. However, an unoptimized query gives rise to significant performance degradation and can cause poor user experience. A poorly written query can not only be slow but also very expensive. The primary source of cost is caused by scanning more data than necessary. This causes a high I/O cost, network traffic as well as CPU processing time.
This article will help to identify the bottleneck which causes the SQL Server performance degradation. With inputs from GeoPITS DBA experts, we have explained the various techniques and tips to optimize your SQL queries.
How can you identify the slow SQL queries?
When it comes to SQL Query Performance, the leading cause for bottlenecks are always slow-moving queries. Slow queries have always plagued the database since its inception. Sometimes, it could be caused by poorly written SQL statements. While other times, it could be caused by excessive RDBMS overhead, due to lack of indexing or query optimization.
We take the scenario of high CPU usage caused by SQL queries. First, identify whether SQL consumes high CPU or not by using task manager or TSQL scripts.
Query to help you to get the list of the top 10 currently running high CPU utilization queries
SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
Query to help to get the list of top 10 history high CPU utilization queries
SELECT TOP 10
QT.text ,
QP.query_plan ,
QS.plan_generation_num ,
QS.creation_time ,
QS.last_execution_time ,
QS.execution_count ,
QS.total_worker_time ,
QS.last_worker_time ,
QS.min_worker_time ,
QS.max_worker_time ,
QS.total_physical_reads ,
QS.last_physical_reads ,
QS.min_physical_reads ,
QS.max_physical_reads ,
QS.total_logical_writes ,
QS.last_logical_writes ,
QS.min_logical_writes ,
QS.max_logical_writes ,
QS.total_logical_reads ,
QS.last_logical_reads ,
QS.min_logical_reads ,
QS.max_logical_reads ,
QS.total_elapsed_time ,
QS.last_elapsed_time ,
QS.min_elapsed_time ,
QS.max_elapsed_time ,
QS.total_rows ,
QS.last_rows ,
QS.min_rows ,
QS.max_rows
FROM
sys.dm_exec_query_stats AS QS
CROSS APPLY
sys.dm_exec_query_plan (QS.plan_handle) AS QP
CROSS APPLY
sys.dm_exec_sql_text (QS.plan_handle) AS QT
ORDER BY QS.last_worker_time DESC
GO
13 techniques to optimize SQL query for better database performance
Query optimization is the process of optimizing SQL code for maximum performance. Improving SQL queries with Query Optimizer leads to a more effective and efficient way of running queries in your database server. This can help you improve your website speed, generate fewer errors, and reduce network traffic by a lot.
Based on the output from the previous activity, implement these 13 techniques to optimize SQL queries to achieve better database performance.
- Make sure your hardware is right fit for your workload
- Update Statistics: If the table has millions of records. SQL Optimizer will have this stored in the form of statistics object. But many a times optimizer will not have the actual reflected data on an updated manner about the row count. As a result SQL Optimizer will create a poor plan. So Update statistics will fix this problem. This will lead to good performance outcome.
Identify the tables which are the part of long running or high CPU utilising queries and perform update statistics for that tables
To identify the statistics on specific table,
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID(Tablename');
- Check open transactions
DBCC OPENTRAN()
-
Check long running jobs
DECLARE @HistoryStartDate datetime
nbsp; ,@HistoryEndDate datetime
,@MinHistExecutions int
,@MinAvgSecsDuration int
SET @HistoryStartDate = '19000101'
SET @HistoryEndDate = GETDATE()
SET @MinHistExecutions = 1.0
SET @MinAvgSecsDuration = 1.0
DECLARE @currently_running_jobs TABLE (
job_id UNIQUEIDENTIFIER NOT NULL
,last_run_date INT NOT NULL
,last_run_time INT NOT NULL
,next_run_date INT NOT NULL
,next_run_time INT NOT NULL
,next_run_schedule_id INT NOT NULL
,requested_to_run INT NOT NULL
,request_source INT NOT NULL
,request_source_id SYSNAME NULL
,running INT NOT NULL
,current_step INT NOT NULL
,current_retry_attempt INT NOT NULL
,job_state INT NOT NULL
)
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'
;WITH JobHistData AS
(
SELECT job_id
,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
,secs_duration=run_duration/10000*3600
+run_duration%10000/100*60
+run_duration%100
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0 --Job Outcome
AND run_status = 1 --Succeeded
)
,JobHistStats AS
(
SELECT job_id
,AvgDuration = AVG(secs_duration*1.)
,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
FROM JobHistData
WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101') GROUP BY job_id HAVING COUNT(*) >= @MinHistExecutions
AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)
SELECT jd.job_id
,j.name AS [JobName]
,MAX(act.start_execution_date) AS [ExecutionDate]
,AvgDuration AS [Historical Avg Duration (secs)]
,AvgPlus2StDev AS [Min Threshhold (secs)]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
WHERE secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev
AND crj.job_state = 1
GROUP BY jd.job_id, j.name, AvgDuration, AvgPlus2StDev
Credit: Author: Thomas LaRock
- Check queries are currently waiting for: It is important to understand WaitType in query queues. When there is a query waiting for something to be completed, we can classify wait type on the same. Example : CXPackage, NetworkIO, Latch, Lock - these are all common wait time. By understanding the Wait Type we can also optimize the same.
SELECT DES.host_name,
DES.program_name,
DES.login_name,
DOWT.session_id,
DOWT.wait_duration_ms,
DOWT.wait_type,
DOWT.blocking_session_id,
DOWT.resource_description,
SUBSTRING
(
DEST.text,
DER.statement_start_offset / 2 - 1,
CASE WHEN DER.statement_end_offset = -1
THEN DATALENGTH(DEST.text)
ELSE (DER.statement_end_offset - DER.statement_start_offset) / 2 + 1
END
) AS SQLCommand,
DEST.text
FROM sys.dm_exec_sessions AS DES
LEFT JOIN sys.dm_os_waiting_tasks DOWT
ON(DES.session_id = DOWT.session_id)
INNER JOIN sys.dm_exec_requests AS DER
ON (DOWT.session_id = DER.session_id)
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST
WHERE DOWT.session_id IN
(
SELECT DES.session_id
FROM sys.dm_exec_sessions AS DES
WHERE DES.is_user_process = 1
)
UNION ALL
SELECT DES.host_name,
DES.program_name,
DES.login_name,
DES.session_id,
DER.wait_time,
DER.wait_type,
DER.blocking_session_id,
DER.wait_resource,
SUBSTRING
(
DEST.text,
DER.statement_start_offset / 2,
CASE WHEN DER.statement_end_offset = -1
THEN DATALENGTH(DEST.text)
ELSE (DER.statement_end_offset - DER.statement_start_offset) / 2
END
) AS SQLCommand,
DEST.text
FROM sys.dm_exec_sessions AS DES
LEFT JOIN sys.dm_exec_requests AS DER
ON (DES.session_id = DER.session_id)
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST
WHERE DER.session_id IN (SELECT blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL)
--ORDER BY
-- DER.session_id;
Use activity monitor to track the current expensive queries
- Make sure your replication sync or availability group is healthy
- Check if any IO encounter: This will occur if there is any disk issue or error. How to check for this? In the SQL server Error Logs.
- Validate error log for any critical errors
- Make sure sufficient disk space is available
- Identify the missing indexes or right indexes by analyzing the long running queries.
- Configure query store to track regressive queries
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id;
- Check for any deadlock in SQL server.
Advantages of SQL query optimization
SQL query optimization is very important for each and every company that has to deal with a large number of users. It doesn't only save your precious time but also improves your performance. Here are the top advantages of SQL query optimization.
- It reduces the execution time of database queries and overall database performance.
- It limits the length and complexity of queries.
- It improves application response time and reduce load on CPU
- It can enhance the database security by fetching only required columns to the user.
- It manipulates the data within the database whenever possible.
Conclusion: Optimizing your SQL queries can help make your system more efficient.
In this article we covered a lot about optimizing your SQL queries, but the truth is there's always more to learn. Keep up with the GeoPITS blog to get the most out of your databases.
GeoPITS specializes in SQL Server database administration, performance tuning, problem-solving, and training for in-depth SQL Server Database knowledge. If you're planning any type of migration especially to Azure SQL Database, GeoPITS can assist you in planning and implementing a complete database migration. Please contact us for more information.
