SQLServer服务器插入慢

DBCC SQLPERF("waitstats")

SET STATISTICS IO ON
and
SET STATISTICS PROFILE ON
STATISTICS IO 统计
Can be useful in telling you which tables it is doing the most amount of table scans, logical reads & physical reads (I use these three to focus on which part of the query plan needs the most tuning)会告知你哪个表在如下过程中占用的时长最长，过程有：表扫描、逻辑读和物理读（我使用这3个参数来关注查询方案的哪个部分需要最多调整）
STATISTICS PROFILE
Will primarily return the query plan in a tabular format, you can then look at the IO and CPU columns for what is costing the most amount in the query (is it the table scan on your temp table vs the sort it does to insert into your clustered key, etc...)主要以表格形式返回查询方案性能分析，你能查看IO和CPU列，找出最费时的查询语句（是临时表的表扫描或插入到簇键，等等）

analyze query execution plan and check for index scans, table scans, usage of convert_implicit functions for sql data types, parallelism.分析查询执行方案和检查索引扫描、表扫描、内在转换函数处理sql数据类型的用法和并行性
run the query with SET STATISTICS IO ON and SET STATISTICS TIME ON to see the execution time and read/write io for each insert.带SET STATISTICS IO ON 和SET STATISTICS TIME ON来执行查询来检查执行时间和每个插入的读写IO。
check out waittime from sysprocesses for your session spid.检查sysprocesses得来的waittime，关注目标是你的事务的spid。
run profiler and select standard template. select following: Performance statistics (if repeated then your plan is compiled many times - not good), RPC:completed, SQL:batchcompleted and SQL:batchstarting. Add to them column rowcounts to see exactly the number of rows in the batch. Filter the results to see only your query.
at last collect Page Life Expectancy counter from windows perfmon and if it is below 300 (5 min) then the SQL has low memory. Also collect disk counters: disk queue length, Disk Time (your data files drive), Disk Time (your Log files drive) to see if there is pressure on disks.

1. COMMIT XSN (or ABORT XSN)
2. CHECKPOINT
3. LOG Block is full

实际操作

修改了内存最大，每个查询大小

查询等待结果

WRITELOG 3091389 7.321531E+07 229212

SOS_SCHEDULER_YIELD 4898103 37970 37206

ASYNC_NETWORK_IO 391584 249976 20389

LAZYWRITER_SLEEP 1527662 1.520963E+09 7768

LCK_M_U 16156 303000 6224

select * from sys.dm_os_performance_counters where counter_name like '%Log%'

公司内机器每秒50个请求,WRITELOG小,IO请求小.机房慢.解释:服务器压力大,,公司内压力低,效率高.机房机器硬盘是RAID1.
因为长时间慢,还是磁盘自己慢

友情链接

汕头招聘网 | 山东招聘网 | 郑州教育培训 | 软件下载