2012年11月 的存档
2012十一月27

schema

sql server 评论关闭
In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In today's post I will cover schemas. Schemas were introduced in SQL Server 2005, each schema is basically a distinct namespace in a database. A schema exists independently of the database user who created it. A schema is simply a container of objects. The owner of a schema can be any user, the ownership of the schema is transferable.

Let's see how this all works, first create a new login name Denis with a highly secure password

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE master
GO
CREATE LOGIN Denis WITH PASSWORD = 'Bla'
GO

To run all this code correctly, you should have two connections to the database we will create, one connection should be your admin connection, the other connection should be connected as this new user we just created.

Now create a new database named SalesStuff

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE DATABASE SalesStuff
GO

Inside the SalesStuff database create a new user which is mapped to the login Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE SalesStuff
GO
CREATE USER Denis FOR LOGIN Denis
GO

Create a schema in the SalesStuff database named Sales, also create a table named Orders in that schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
(OrderID int, OrderDate date, OrderAmount decimal(30,2))

Now login to the database with the Denis account and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from orders

You should see the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'orders'.

The problem is that when you login, your default schema is not Sales and so the Orders table can't be found. Prefix the table with the schema and try again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders

You get this error message
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

We need to give the Denis user select permissions for this table. Login as the admin and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT SELECT ON SCHEMA::Sales TO Denis

That query gave the user Denis select permissions on all tables in the Sales schema. Notice the double colon syntax, that is how you need to grant, deny and revoke permissions. If you run the select query again, you will get back an empty resultset.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders

Let's try to do an insert

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)

As expected, that fails also

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

Go back to the admin query window, run the query below to give the insert permissions

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT INSERT ON SCHEMA::Sales TO Denis

If you try the insert again, it will succeed

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)

Remember how we tried to select from the table without specifying the schema? Let's try that again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.

Same error, let's fix that

Go back to the admin query window and execute the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
ALTER USER Denis
WITH DEFAULT_SCHEMA = Sales

We just made the Sales schema the default schema for the user Denis. Now if we specify the schema or if we omit the schema, we get back the same result

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders
select * from Sales.Orders

Go back to the admin connection and create this stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest1
as
select 1

Go to the query window for the user Denis and run the proc

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1

Msg 229, Level 14, State 5, Procedure prtest1, Line 1
The EXECUTE permission was denied on the object 'prtest1', database 'SalesStuff', schema 'dbo'.

As you can see, we don't have execute permissions for the stored procedure.
Bring up the admin query window and give Denis execute permissions on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT execute ON SCHEMA::Sales TO Denis

Now if you try to execute the proc from the connection which is logged in as Denis it succeeds

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1

Go back yet again to the admin query window and create another stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest2
as
select 2

Now if you go back to the connection for user Denis and execute the proc we just created, it also is successful.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest2

As you can see, once you have execute permissions on a schema, you don't have to go and explicitly give execute permissions for every stored procedure

To see all the tables that you have select permissions on, you can run the query below from the connection logged in as Denis. It will return 1 if you have select permissions or 0 if you don't

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, name FROM sys.tables

Output
---------------
1	Orders

For procs it will return 1 if you have execute permissions, if you don't have execute permissions then the proc is not returned. Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures

Output

---------------
1	prtest1

1	prtest2

As you can see you get 2 rows back

No go back to the admin connection and deny execute on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
DENY EXECUTE ON SCHEMA::Sales TO Denis

Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures

As you can see nothing is returned at all

So what is so cool about schemas anyway?
When you start using schemas, you have a way to logically group a bunch of objects together. For example if you have a Sales and a Marketing schema then if you need to find a specific table that has something to do with Sales, you don't have to look up and down in object explorer to find the table, it should be sorted under the sales schema. Permissions are also easier, you give the sales person permission to the Sales schema and if new tables are added he or she will have the select permission the moment the table is created.
When using schemas you now can have a table named Customers in both schemas without a problem and each will hold data just for the department that uses the schema the table is in.

Read more
This was just a small overview, I did not cover all the things you need to know about schemas in SQL Server. Take a look at SQL Server Best Practices – Implementation of Database Object Schemas to get some more details about how to use schemas.

参考:http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-advent-2011-day-4
2012十一月27

获取执行的上下文

sql server 评论关闭
select APP_NAME()
SELECT Host_Name()
SELECT @@SERVERNAME
–可用于触发器监控
2012十一月20

估算每天数据库的更新量

sql server 评论关闭
--估算每天数据库的更新量

DECLARE @starttime DATETIME
SELECT @starttime = sqlserver_start_time
FROM sys.dm_os_sys_info --取服务器启动时间

--按索引表中最大的更新量来估算
SELECT name,user_updates,last_user_update, 日平均
FROM (
	SELECT
	tbl.name,user_updates
	,ius.last_user_update,
	user_updates/(DATEDIFF(mi,@starttime,last_user_update)*1.0/(24*60)) 日平均
	,ROW_NUMBER() OVER( PARTITION BY name ORDER BY user_updates DESC )n
	FROM
	sys.dm_db_index_usage_stats ius
	INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = ius.OBJECT_ID
	WHERE  ius.user_updates !=0 AND ius.database_id = DB_ID()
)m
WHERE m.n = 1
ORDER BY m.日平均 DESC
2012十一月20

复制分发中查看命令

sql server 评论关闭
 SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX))
 FROM msrepl_commands
 WHERE xact_seqno = 0x0000EFC4000018920027
 AND command_id = 1

 --
 SELECT xact_seqno,COUNT(1) c
 FROM  dbo.MSrepl_commands
 GROUP BY xact_seqno
 ORDER BY c DESC

--或者用sp_browsereplcmds
sp_browsereplcmds '0x0000EFC4000018920027','0x00008920027'

					
2012十一月19

server服务启动不了 错误5:拒绝被访问

windows server 评论关闭

解决方法:
a. 运行MSCONFIG
b. 在常规下选择 选择性启动
c. 然后清除Process System.ini File, Process Win.ini File和Load Startup Items 的复选框,但是保留使用原始Boot.ini。
d. 在服务下,先点隐藏所有windows 服务,然后选择 disable all.
e. 然后重新启动。观察问题是否依旧发生。

2012十一月17

更快的distinct

USE     tempdb;
GO
DROP    TABLE dbo.Test;
GO
CREATE  TABLE
        dbo.Test
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT  dbo.Test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO

SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT
        data
FROM    dbo.Test;

SQL Server 分析和编译时间:
   CPU 时间 = 859 毫秒,占用时间 = 2702 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

(43 行受影响)

SQL Server 执行时间:
   CPU 时间 = 967 毫秒,占用时间 = 5881 毫秒。

-- 15ms CPU
WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    dbo.Test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    dbo.Test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE

--OPTION  (MAXRECURSION 0);

在有索引且排好序的情况下,后者速度更快!

SET     STATISTICS TIME OFF;
GO
DROP    TABLE dbo.Test;
2012十一月17

去重的多种方法

sql server 评论关闭
Create table #sales_details
(
	sales_id int identity(1,1),
	item_id int not null,
	qty int not null,
	unit_price decimal(12,2) not null,
	sales_date datetime not null
)

insert into #sales_details (item_id,qty,unit_price,sales_date)
select 1001,5,200,'2012-09-03 11:16:28' union all
select 1001,2,200,'2012-09-04 19:22:11' union all
select 1002,15,1300,'2012-09-06 14:26:40' union all
select 1003,8,78,'2012-09-19 15:11:19' union all
select 1001,6,200,'2012-09-22 16:36:11' union all
select 1004,22,1000,'2012-09-23 16:51:34' union all
select 1004,11,1000,'2012-09-23 17:29:38' union all
select 1002,29,1300,'2012-09-23 18:20:10' union all
select 1002,6,1300,'2012-09-26 19:40:41' union all
select 1002,33,1300,'2012-09-30 20:26:29' 

Assume that you want to find out distinct item_id from the above table. You can use many methods. Some of them are listed below

--Method 1 : Use DISTINCT keyword

select distinct item_id from #sales_details 

--Method 2 : Use GROUP BY Clause
select item_id from #sales_details
Group by item_id

--Method 3 : UNION the same table
select item_id from #sales_details
UNION
select item_id from #sales_details 

--Method 4 : UNION the same table with not selecting any rows from secondly sepecified table
select item_id from #sales_details
UNION    --union会做一个distinct
select item_id from #sales_details where 1=0

--Method 5 : UNION the table with Empty result

select distinct item_id from #sales_details 

select item_id from #sales_details
UNION
select 0 where 1=0

--Method 6 : Use Row_number() function
select item_id from
(
	select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details
) as t
where sno=1

参考:distinct
2012十一月15

多状态一字段存储

设计好的思路 评论关闭
--多状态一字段存储,避免增加状态需要再新增字段的事情。sqlserver中set值即是类似存储
--创建状态表,用二进制上的1标识是否,生成身份值在建立表是指定,据身份值取状态,只需进行与运算即可
--可扩大,比如8进制,每位上可有8个选择,--假如有7种状态,则可用一个字段标识出56种选择
DROP TABLE #MyStatus
GO

CREATE TABLE #MyStatus( StatusName NVARCHAR(100),StatusValue INT )
INSERT INTO #MyStatus
VALUES('是否高',1) --1,001 二进制数位
INSERT INTO #MyStatus
VALUES('是否富',2) --2,010
INSERT INTO #MyStatus
VALUES('是否帅',4) --4,100

SELECT *
FROM #MyStatus

--生成身份值
--张三
WITH zhangsan AS(
	SELECT '是否高' StatusName,1 StatusValue
	UNION SELECT '是否富' StatusName,0 StatusValue
	UNION SELECT '是否帅' StatusName,1 StatusValue
)
SELECT SUM(m.StatusValue) 张三身份值
FROM zhangsan z
JOIN #MyStatus m ON z.StatusName = m.StatusName
WHERE Z.StatusValue = 1

--据身份值取出状态,李四身份值为3,查询各状态
SELECT m.StatusName,
CASE WHEN m.StatusValue & 3 = m.StatusValue THEN '是'
ELSE '否' END
FROM #MyStatus m
2012十一月15

sql调优步骤

sql server 评论关闭

一,入手:
拿到存储过程后,先执行一次看速度与编译时间,确定是编译慢还是执行问题。

SET STATISTICS TIME ON 
Exec dbo.bdOrderTrace_sel2 
@TranType='HasStatusPassed',@OrderNo='L121114BDNAW2',@OrderStatus='33'

1)若是编译慢,说明编译计划没有重用,在频繁的重新生成,多出于大型存储过程,此时可将sql移出单独写。
或者分析影响执行计划频繁生成的因素。http://enjoyasp.net/?p=2131

2)若执行很快,但正式环境还是很慢,可能发生了参数嗅探问题,参考:http://enjoyasp.net/?p=2161

3)若不是编译问题,也不是参数嗅探问题,那说明sql语句要优化了
   (1)分析逻辑,去除不必要的表关联,不必要的字段select
   (2)用数据库引擎优化顾问来给出索引建议

2012十一月13

服务器推送信息到客户端signalR

ASP.NET 评论关闭

Signal 是微软支持的一个运行在 Dot NET 平台上的 html websocket 框架。它出现的主要目的是实现服务器主动推送(Push)消息到客户端页面,这样客户端就不必重新发送请求或使用轮询技术来获取消息。
http://blog.csdn.net/kesalin/article/details/8166925

2012十一月9

SQLSERVER查看权限

sql server 评论关闭
--查看当前库的账号

SELECT  members.name, roles.name,roles.type_desc,members.type_desc
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
ORDER BY members.name

EXEC dbo.sp_helprolemember
2012十一月8

sqlserver2012 tsql增强

sql server 评论关闭
T-SQL Enhancements
参考:英文中文

1,WITH RESULT SETS
更改存储过程返回结果表的字段名类型
CREATE PROCEDURE Denali_WithResultSet  AS
BEGIN
    SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature
    UNION ALL  SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature
    UNION ALL SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature
    UNION ALL SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature
END
GO
EXEC Denali_WithResultSet
WITH RESULT SETS  (( No int,FeatureType varchar(50), FeatureName varchar(50) )   )

2,OFFSET and FETCH
offset过滤几行 fetch:取下面的几行
SELECT top 15 * FROM dbo.frmuser f(NOLOCK) order BY account
SELECT * FROM dbo.frmuser f(NOLOCK) order BY account OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
SELECT top 5 * FROM ( SELECT * ,ROW_NUMBER()OVER (ORDER BY account)n FROM frmuser f)m WHERE m.n>10

3,SEQUENCE
提供自增长,与identity不同的是不依赖与表,并且是全局性的 可不设置最大值,若到最大值时可自动循环。
CREATE SEQUENCE SeqRange  AS int   START WITH 1   INCREMENT BY 1     MINVALUE 1      MAXVALUE 5    CYCLE ;//循环自动
SELECT NEXT VALUE FOR dbo.SeqRange  S
ELECT NEXT VALUE FOR dbo.Seq;
 SELECT NEXT VALUE FOR dbo.Seq;

到达最大值后,可重置
ALTER SEQUENCE seq  RESTART WITH 1

赋值:
DECLARE @I INT
SELECT @I = NEXT VALUE FOR dbo.SeqRange
SELECT @i

查询当前值

SELECT cache_size, current_value 
FROM sys.sequences
WHERE name = 'SeqRange' ;
 

相关存储过程
DECLARE @first_value sql_variant,
@last_value sql_variant
EXEC sp_sequence_get_range @sequence_name = N’SeqRange’, @range_size = 4, @range_first_value = @first_value OUTPUT, @range_last_value = @last_value OUTPUT;
SELECT @first_value AS FirstNumber, @last_value as LastNumber

应用:订单号生成
可在表定义时使用,这样我们就能在插入之前获取ID
create table t1 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) )

4,FORCESEEK and FORCESCAN
forceseek:强制查询按某个索引查找
forecescan:强制查询按查个索引扫描

5,
EOMONTH:返回当月最后一天
CHOOSE:返回列表中的第几个值
Select Choose (2, 'January', 'February', 'March');
IIF:三值逻辑
SELECT IIF (@a > @b, 'TRUE', ‘FALSE’) AS Result;
CONCAT: 连接,可多种类型,如整型与字符串相加
SELECT CONCAT ('age:',1) AS Result;

OVER 增强
unbounded preceding:从头开始
CURRENT ROW:当前行
unbounded following:尾

如:
SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

6,分析函数
lag:返回按某种排序相比当前行,上几行的字段 LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
SELECT TOP 10 Account,lag(account,1,'开始') OVER( ORDER BY account )
FROM dbo.frmuser
ORDER BY account

SELECT TOP 10 Account,lead(account+'aaa',1,'开始') OVER( ORDER BY account )
leag:返回按某种排序相比当前行,下几行的字段
LAST_VALUE 返回 SQL Server 2012 中有序值集中的最后一个值,这个集可用range指定 默认是从第一行到当前这一行:RANGE BETWEEN unbounded preceding AND CURRENT ROW FIRST_VALUE 开头一个值
–需求:取出客户的最近一次购买的订单号与订单金额

SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

–原方法
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
AND bo.orderdate = ( SELECT MAX(OrderDate)
FROM dbo.bdOrder bo2(NOLOCK)
WHERE bo2.CustomerID = bo.CustomerID)
ORDER BY CustomerName,OrderDate

2012十一月5

内存管理

sql server 评论关闭

一、内存
1.查看整体内存使用情况
select 
type,
sum(virtual_memory_reserved_kb)/1024 as [VM Reserved],
sum(virtual_memory_committed_kb) as [VM Committed],
sum(awe_allocated_kb) as [AWE Allocated],
sum(shared_memory_reserved_kb) as [SM Reserved], 
sum(shared_memory_committed_kb) as [SM Committed],
sum(multi_pages_kb) as [MultiPage Allocator],
sum(single_pages_kb) as [SinlgePage Allocator]
from 
sys.dm_os_memory_clerks 
group by type
order by [VM Reserved] DESC 
 
其中,MEMORYCLERK_SQLBUFFERPOOL就是表数据,执行计划的缓存,占用的最大。
分析MEMORYCLERK_SQLBUFFERPOOL有哪些数据,采用如下方法:
 
1)下面的这组语句,就可以打印出当前内存里缓存的所有页面的统计信息。
declare @name nvarchar(100)
declare @cmd nvarchar(1000)
declare dbnames cursor for
select name from master.dbo.sysdatabases
open dbnames
fetch next from dbnames into @name
while @@fetch_status = 0
begin
set @cmd = 'select b.database_id, db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from ' + @name + '.sys.allocation_units a, '
+ @name + '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id(''' + @name + ''')
group by b.database_id,p.object_id, p.index_id
order by b.database_id, buffer_count desc'
exec (@cmd)
fetch next from dbnames into @name
end
close dbnames
deallocate dbnames
go
 
2) 在一条语句第一次执行前后各运行一遍上面的脚本,就能够知道这句话要读入多少数据到内存里。
例如如果运行下面的脚本:
dbcc dropcleanbuffers
go
—-Copy the previous scripts here
Go
use adventureworks
go
select * from person.address
go
—-Copy the previous scripts again here
Go
 
3) 用下面的查询可以得到各种对象各占了多少内存:
select objtype, sum(size_in_bytes) as sum_size_in_bytes, count(bucketid) as cache_counts
from sys.dm_exec_cached_plans
group by objtype
 
4) 如果想要分析具体存储了哪些对象,可以使用下面的语句。但是要注意把结果集输出到一个文件里,因为这个查询的结果在一个生产服务器上会很大的。如果要输出到Management Studio里,对运行这个查询的那台机器的资源会有争用,进而影响到同一台机器上的SQL Server运行。
SELECT usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY objtype DESC;
GO
 
二 使用DMV分析SQL Server启动以来做read最多的语句
1. 按照物理读的页面数排序,前50名。
SELECT TOP 50
qs.total_physical_reads,qs.execution_count,
        qs.total_physical_reads /qs.execution_count as [Avg IO],
            SUBSTRING(qt.text,qs.statement_start_offset/2, 
(case when qs.statement_end_offset = -1 
then len(convert(nvarchar(max), qt.text)) * 2 
else qs.statement_end_offset end -qs.statement_start_offset)/2) 
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_physical_reads desc
 
2. 按照逻辑读的页面数排序,前50名。
SELECT TOP 50
qs.total_logical_reads,qs.execution_count,
        qs.total_logical_reads /qs.execution_count as [Avg IO],
            SUBSTRING(qt.text,qs.statement_start_offset/2, 
(case when qs.statement_end_offset = -1 
then len(convert(nvarchar(max), qt.text)) * 2 
else qs.statement_end_offset end -qs.statement_start_offset)/2) 
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_logical_reads desc
 
3. 使用SQL Trace文件来分析某一段时间内做read最多的语句。
例如现在在c:\sample目录下收集了一个问题时段的trace文件,叫A.trc。第一步要将里面所有的存储过程和批命令执行完成的记录保存到SQL Server里。
select * into Sample
from fn_trace_gettable('c:\sample\a.trc',default)
where eventclass in (10, 12)
 
语句执行完了以后,可以用下面的查询看看里面的数据长什么样。
Select top 1000 textdata, databaseId, HostName, ApplicationName, LoginName, SPID,
Starttime, EndTime, Duration, reads, writes, CPU 
from sample
 
a. 找到是哪台客户端服务器上的哪个应用发过来的语句,从整体上讲在数据库上引起的读最多。
select databaseId,HostName,ApplicationName, sum(reads)
from sample
group by databaseId,HostName,ApplicationName
order by sum(reads) desc
 
b. 按照作的reads从大到小排序,最大的1000个语句。
select top 1000 textdata, databaseId, HostName, ApplicationName, LoginName, SPID,
Starttime, EndTime, Duration, reads, writes, CPU 
from sample
order by reads desc
 
2012十一月2

清除下拉列表的实例名称

sql server 评论关闭
For SQL 2005, delete the file:
02. 
03.C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat
04. 
05.For SQL 2008, the file location, format and name changed:
06. 
07.C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin
08. 
09. 
10. 

  1. Shut down all instances of SSMS
  2. Delete/Rename the file
  3. Open SSMS
11. 
12.For Windows 7, it's in the following:
13. 
14.SQL 2005:
15.C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat
16. 
17.SQL 2008:
18.C:\Users\\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

 

2012十一月2

查询有数据更新的表

sql server 评论关闭
For many reasons SQL Server DBAs need to find the last date and time of an update on a sql table. The SQL Server DMV sys.dm_db_index_usage_stats  can supply enough information to allow the DBA to make a good estimate of the time.

In this case I needed to create a list candidates for tables to be deleted. The data became bloated with a number of different temp tables .

The one drawback of using the DMV is that data is refreshed after every SQL Server restart.

This script lists all tables in the current database, with various columns assisting in deciding the last update.

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()
last_user_update - Time of last user update.
user_updates - Number of updates by user queries.
last_user_seek - Time of last user seek .
last_user_scan - Time of last user scan.
last_user_lookup - Time of last user lookup.
user_seeks - Number of seeks by user queries.
user_scans - Number of scans by user queries.
user_lookups - Number of bookmark lookups by user queries

Related Posts

SQL Server – Last DML operation

SQL Server - Calculate table size with existing data

How to request SQL Server troubleshooting