日志列表 SQL SERVER,商业智能,BI,DBA - 空间

get the top-10 queries that are taking the most CPU

seawater001 6/9/2010 2:04:02 AM 【评论:0】【阅读:75】

SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 &n...

阅读全文 >>

Figure out which databases involve most of the I/O and I/O s

seawater001 5/12/2010 2:06:19 AM 【评论:0】【阅读:81】

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&n...

阅读全文 >>

How to isolate the top waits for sqlserver

seawater001 5/11/2010 9:43:17 PM 【评论:0】【阅读:63】

WITH Waits AS ( SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE '%SLEEP%' -- filter out additional irrelevant waits ) SELECT W1.wait_type, CAST(

阅读全文 >>

Split A String Into A Table

seawater001 4/7/2010 10:02:19 PM 【评论:0】【阅读:83】

declare @TableStr varchar(max)Declare @SplitStr VARCHAR(1) Set @TableStr='AIU;Aptimus;CTU;Datamark;Eminata;IADT;Kaplan;Triad;'SET @SplitStr=';' SELECT SUBSTRING((@SplitStr+@TableStr+@SplitStr),n.number+1,CHARINDEX(@SplitStr,(@SplitStr+@TableStr+@SplitStr),n.number+1)-n.Number-1) As ItemFrom (select distinct number from master..spt_valueswhere num...

阅读全文 >>

Summary A String Column

seawater001 4/7/2010 9:12:25 PM 【评论:0】【阅读:89】

declare @Result nvarchar(max) Set @Result='' select @Result=@Result+';'+StringColumn from TableName order by SomeColumn select @Result

阅读全文 >>

Get A Random Integer

seawater001 3/31/2010 2:18:51 AM 【评论:0】【阅读:78】

select cast(rand()*N as int) when N>0, The data range is between 0 and N-1 when N<0, the data range is between N and -1

阅读全文 >>

Get the row count and data size of all tables

seawater001 3/30/2010 2:13:51 AM 【评论:0】【阅读:96】

declare @spacetable table( name nvarchar(255) ,rows int ,reserved nvarchar(255) ,data nvarchar(255) ,index_size nvarchar(255) ,unused nvarchar(255)) declare @tablename nvarchar(255) declare Mycursor cursor forselect schema_name(schema_id)+'.'+Na...

阅读全文 >>

USE CTE ORDER Department For TreeView

seawater001 3/25/2010 2:28:40 AM 【评论:0】【阅读:93】

SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees; GO CREATE TABLE dbo.Employees ( id INT NOT NULL PRIMARY KEY, pid INT NULL , num decimal ...

阅读全文 >>

回复：我的原创整理请大家指教第一篇 SQL 与 CPU (续篇,重编译)

caierjia 3/31/2009 4:28:35 PM 【评论:1】【阅读:454】

1 引起编译重编译的原因A 架构变化B 统计变化C 延期编译D Set属性改变E 临时表改变F 存储过程创建时使用了RECOMPLIE查询提示或使用了OPTION （RECOMPILE）。2 通过系统计数器监视A SQL Statistics : SQL Recompilations/sec和Batch Request/sec的比值应该很低，除非用户提交了大量的单独查询。B SQL Server: SQL Statistics: Batch Requests/sec(每秒收到的 Transact-SQL 命令批数。这一统计信息受所有约束（如 I/O、用户数、高速缓存大小、请求的复杂程度等）影响。批处理请求数值高意味着吞吐量很好。)C SQL Server: SQL Statistics:

阅读全文 >>

欢迎使用微软BI开拓者个人空间

caierjia 3/31/2009 4:28:35 PM 【评论:0】【阅读:452】

????????

阅读全文 >>

在SSAS中的数据源视图中添加命名计算的问题

YinPSoft 11/26/2008 6:18:16 PM 【评论:0】【阅读:247】

在数据源视图中添加命名计算，想新添加一个计算列，通过使用CASE语句手动地离散化另一个连续的数值列。但是始终要报个错：“ 出现以下系统错误: 帐户名与安全标识间无任何映射完成”？如图：[attachimg]758[/attachimg]这个解决方案是我从网上下载的一个案例，其实每当我把一台机器上的SSAS解决方案移到另一台机器上时都会发生这个问题，不知道是什么原因我这个CASE语句是按照书上的例写的，语法上应该不会有问题。语句如下： CASE WHEN [Num Bedrooms] = 1 THEN 'One' WHEN [Num Bedrooms] <= 3 THEN 'Two or Three' WHEN [Num Bedrooms] >=4 THEN 'Fo