2. Top with Ties 选项 select top 100 with ties name, id from test1 order by name desc & select top 100 name, id from test1 order by name desc 上一个语句只会显示出所有包含 name 的行，不管有没有超过 100 的限制。 With ties 的任务是将绑定的字段全部行显示出来。
3. MSSQL 处理语句步骤 Select 语句步骤： Select Distinct top 1 id,name from lefttable l inner Join righttable r on i.id=r.id Where r.id >100 Group by department With (cube) Having Order by r.id 对两个表进行笛卡尔积计算交叉连接 应用 on 筛选器，过滤不符合条件的俄行，然后如果是外连接，则将后添加外部行 再对当前虚拟表进行条件筛选 对虚拟表进行分组 生成超组虚拟表 对 group 后的表进行再筛选 处理 select 列表 重复行移除 排序 返回指定数量行
4. 推演的部分问题 在 select 列表没有处理以前，是不可以使用字段别名的 On 条件的应用在外连接的情况下并不是最终的结果，而需应用 where 筛选内连接的话是没有添加外部行这个步骤，所以条件在 on 或者 where 处处理都会得到一样的结果 Where 条件不能应用 group 条件，因为他在分组以前就被处理了
8. CTE 递归处理 with empcte as ( select employeeid,reportsto,firstname,lastname from dbo.employees where employeeid = 2 union all select emp.employeeid,emp.reportsto,emp.firstname,emp.lastname from empcte as mgr join dbo.employees as emp on emp.reportsto = mgr.employeeid ) select * from empcte option(maxrecursion 2) 为防止递归出现死套，可以指定递归级别，默认为 100.
11. 分类前 N 名问题解决 CREATE TABLE table1 ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null ) select id,name,class,date from( select id,name,class,date ,row_number() over(partition by class order by date desc) as rowindex from table1) a where rowindex <= 5
13. 存储过程编译计划的命中率，以及执行频率 SELECT cacheobjtype, usecounts as Count, cast(C.sql as varChar(max)) as StoredProcedure FROM Master.dbo.syscacheobjects C JOIN Master.dbo.sysdatabases D ON C.dbid = C.dbid WHERE D.Name = DB_Name() -- AND ObjType = 'Adhoc' ORDER BY StoredProcedure 查找出缓存命中率高的存储过程 关键字： syscacheobjects
14. 服务器整体数据库 IO 压力分析 WITH DBIO AS ( SELECT DB_NAME(IVFS.database_id) AS db, CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type, SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io, SUM(IVFS.io_stall) AS io_stall FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS JOIN sys.master_files AS MF ON IVFS.database_id = MF.database_id AND IVFS.file_id = MF.file_id GROUP BY DB_NAME(IVFS.database_id), MF.type ) SELECT db, file_type, CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb, CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s, CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS io_stall_per, ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn FROM DBIO ORDER BY io_stall DESC; 关键字： sys.dm_io_virtual_file_stats
15. 索引利用率分析 select b.name,a.* from sys.dm_db_index_usage_stats a inner join sysindexes b on (a.object_id = b.id) order by a.user_seeks desc 结合表设计和索引，以及表的功能来综合分析索引环境是否合适。 关键字： sys.dm_db_index_usage_stats
16. CPU 耗时分析 SELECT total_cpu_time, total_execution_count, total_cpu_time/total_execution_count as cpu_time_per_execution, number_of_statements, s2.text --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text FROM (SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, COUNT(*) AS number_of_statements, qs.sql_handle --, --MIN(statement_start_offset) AS statement_start_offset, --MAX(statement_end_offset) AS statement_end_offset FROM sys.dm_exec_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time)/SUM(qs.execution_count) DESC) AS stats CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 在数据库 CPU 居高不下的情况下直接对症查找为什么 CPU 会很高。 关键字： sys.dm_exec_query_stats
17. 总体分析执行计划 CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30)) AS SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql CROSS APPLY sys.dm_exec_query_plan(plan_handle) p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")] ') = 1 GO EXEC LookForPhysicalOps 'Clustered Index Scan' EXEC LookForPhysicalOps 'Nested Loops' EXEC LookForPhysicalOps 'Table Scan' 关键字： sys.dm_exec_query_stats
![分类前 N 名问题解决 CREATE TABLE table1 ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [class] int not null, [date] datetime not null ) select id,name,class,date from( select id,name,class,date ,row_number() over(partition by class order by date desc) as rowindex from table1) a where rowindex <= 5](https://image.slidesharecdn.com/sqlserver-091208221303-phpapp01/85/Sql-Server-11-320.jpg)
![总体分析执行计划 CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30)) AS SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql CROSS APPLY sys.dm_exec_query_plan(plan_handle) p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")] ') = 1 GO EXEC LookForPhysicalOps 'Clustered Index Scan' EXEC LookForPhysicalOps 'Nested Loops' EXEC LookForPhysicalOps 'Table Scan' 关键字： sys.dm_exec_query_stats](https://image.slidesharecdn.com/sqlserver-091208221303-phpapp01/85/Sql-Server-17-320.jpg)