|
디스크 읽기 최대성능을 볼 때 쓸만함
BACKUP DATABASE tt
TO DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
,DISK = 'NUL:'
WITH NO_COMPRESSION
,BUFFERCOUNT = 2200
backup log tt to
disk ='NUL'
, disk = 'NUL'
1) call pattern
jTDS 로 PreparedStatement 를 호출하면 sp_prepare sp_execute 를 호출한다.
MS JDBC 는 sp_prepexec 로 위 두 단계를 하나로 줄여서 실행한다. 한마디로 서버 클라이언트간 round-trip 횟수를 줄여 성능을 높일 수 있다. connectionString 에 prepareSQL=2 라고 옵션을 주면 sp_executesql 로 동작해 한번에 돌릴 수 있고 parameter snipping 도 가능하다. 그런데, 이건 좀 다른 동작 방식이다. 항상 sql 이 날아가야 한다.
2) parameter snipping
jTDS 로 PreparedStatement 를 사용하면 파라메터 스니핑을 사용하지 못하고
JDBC 를 사용하면 파라메터 스니핑이 가능하다.....
그런데 감당하지 못하겠으면 SQL Server 레벨에서 disable 가능하다
Parameter sniffing can be disabled using the documented and supported trace flag 4136. This trace flag is also supported for per-query use via the QUERYTRACEON hint. Both apply from SQL Server 2005 Service Pack 4 onward (and slightly earlier if you are willing to apply cumulative updates to Service Pack 3).
sql server 에서 파라메터 스니핑을 하는 경우(MS JDCB)
<ParameterList>
<ColumnReference Column="@P0" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
</ParameterList>
파라메터 스니핑을 못하는 경우 (jTDS)
<ParameterList>
<ColumnReference Column="@P0" ParameterRuntimeValue="(1)" />
</ParameterList>
3) applicationIntent 기능을 MS JDBC 만 지원한다. 4.0 이상부터
고 가용성 기능을 쓰려면 JDBC 로 가야한다.
기타)
unicode false 옵션은 주의해서 셋팅해라
SQL Server Extended Events (xevent) are great to troubleshoot many issues including performance issues or other targeted scenarios. But we keep seeing users misusing them to negatively impact their systems.
Here is a latest example. We had a customer who called our support for an issue where the same query ran fast in test but ran slow in production. In fact, the query ‘never’ finished in production in a sense that they waited for 30 minutes or more but couldn’t get it to finish. But the same query finished in seconds in test.
The query was actually a large batch that contains over 90k lines of code with many statements with size of about 800k. Our initial effort focused on comparing the differences between the two servers. But there weren’t many differences. We even eliminated database as a factor. They restored database from production to test and issue went away in test.
Through some troubleshooting, we discovered even parsing the query took a long time in production. But we simply couldn’t figure out what was going on because the statements in the batch were very simple inserts. So we took some user dumps and analyzed the call stacks. Finally we realized that the XEvent was involved. Every dump we got showed the server was producing XEvent. It turned out their developers enabled the some xevents which can cause high overhead by accident inproduction.
So we got their Xevents being captured (screen shot below). Among those, there were scan_started, scan_stopped, wait_info etc. It was generate a million events every minute without anyone else running the system. In addition to that, this customer captured sql_text for all the events. Basically the same 800k batch text would be captured for every event including wait_info etc
None of the events mentioned (scan_started, scan_stopped, wait_info ) are suited for long term capture. For this specific scenario, it was wait_info that hurt them most (combined with sql_text being included). Wait_info is produced whenever there is a scheduler yield or wait is finished. Because customer’s batch is very large, SQL needs to play nice and yields frequently. so the event gets triggered very frequently. That’s why so many events were generated and overhead led to slowdown.
create and enable Xevents
CREATE EVENT SESSION [test_xevent] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) ),
ADD EVENT sqlos.wait_info_external(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) )
ADD TARGET package0.event_counter,
ADD TARGET package0.event_file(SET filename=N’c:\temp\test_xevent.xel’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
alter event session [test_xevent] on server state = start
in SSMS, duplicate “INSERT INTO t VALUES (null,null,null)” 50,000 times and try parsing the query. it will take several minutes. but if you disable the Xevent session, the entire batch will parse in seconds.
If you have to capture wait_info for large batch like this one, consider taking out sql_text. It will be hard to look at the trace afterwards without it. But hopefully, it is a controlled environment and you know which session you are troubleshooting and filter on that.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server
