|
|
02 2012 档案
摘要：问题： sql server 2000 sp3 数据库复制 报错：{call sp_MSget_repl_commands(6, ?, 0, 7500000)} 违反了 PRIMARY KEY 约束 'PK__@snapshot_seqnos__1B8078A1'。不能在对象 '#1A8C5468' 中插入重复键。Solution:Change of store procedure in distibution: sp_MSget_repl_commandsfrom:declare @snapshot_seqnos table (subscription_seq
阅读全文
摘要：Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as followsPFS (Page Free Space):it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores statu
阅读全文
摘要：1.在sp_configure 配置xp_cmdshell 可运行2.创建一个账号 test ，用来执行 xp_cmdshell 但是非sysadmin权限3.用grant 把 xp_cmdshell 权限赋予 test4.创建代理账号，这个账号是windows 账号。5.在sql server 中 设置代理账号，右击实例属性 安全性下的代理中填入 windows 账号密码 或者使用sp_xp_cmdshell_proxy_account 设置代理账号
阅读全文
摘要：参数化和即席查询，都是一个二难的选择。一旦选择参数化 就有可能带来 一种情况就是 执行计划反复被利用。不会通过收集现有的统计信息，出一个比较优异的执行计划。即席查询也就是 ad hoc query，每次都需要重编译，花费比较大。这个是一个2难，需要平衡。对于这个问题 sql server 有 简单参数化，强制参数化和2008 才有的optimize for ad hoc workloads。optimize for ad hoc workloads 的原理大概如此：当你第一次执行 ad hoc 查询的时候，被编译出来的执行计划，sql server 存个一部分。为什么不全存呢，如果全存，adh
阅读全文
摘要：第01讲：Chapter 01--Overview of Oracle9i Database Performance Tuning第02讲：Chapter 01--Overview of Oracle9i Database Performance Tuning第03讲：Chapter 02--Diagnostic and Tuning Tools第04讲：Chapter 02--Diagnostic and Tuning Tools第05讲：Chapter 02--Diagnostic and Tuning Tools第06讲：Chapter 02--Diagnostic and Tuning
阅读全文
摘要：A Performance Troubleshooting Methodology for SQL Server28 September 2011byJonathan KehayiasWhenhealing a sickSQL Server, you must forget the idea that therecould ever bea simple correspondence between symptom and disease: The art of troubleshooting is much more the art of discovering, and assemblin
阅读全文
摘要：今天在 相同环境测试 2000 和 2008 性能让我意外的是 2008 明显比2000 慢很多测试sql：SET STATISTICS IO ONSET STATISTICS TIME ON SELECT COUNT(1) FROM dbo.tbtext a INNER LOOP JOIN dbo.tbtext b ON a.id = b.id option (maxdop 1)SET STATISTICS IO OffSET STATISTICS TIME Off 表结构：CREATE TABLE [dbo].[tbtext]( [id] [int] IDENTI...
阅读全文
摘要：Investigating perfomance bottlenecks in SQL Server 2008 bylooking at wait events using the XE trace systemIntroduction.In my previous articlesSQL Server Wait Events: Taking the Guesswork out of Performance Profiling, andTaking the Guesswork out of SQL Server Performance Profiling Part 2,I introduced
阅读全文
摘要：Taking the Guesswork out of SQL Server Performance Profiling Part 218 May 2007byMario BroodbakkerIn the previous article,Dude, where's my time, I described a method, based on use of SQL Server wait events, for measuring response time and pinpointing the main bottlenecks in your SQL Server applic
阅读全文
摘要：SQL Server Wait Events: Taking the Guesswork out of Performance Profiling22 March 2007byMario BroodbakkerMeasuring what is actually happening is always the best course of action when investigating performance issues on databases, rather than relying on cache hit ratios, or best practices, or worst o
阅读全文
摘要：非聚集索引和聚集索引的区别不在不会详细说明非聚集索引和聚集索引的结构有什么不一样，因为这种东西网上已经说的很多了。一个表一旦建立了聚集索引，那么那张表就是一张被b树重新组织过的表。而没聚集索引表就是一个堆表。什么是b树，什么是堆表就不解释了。小弟对，非聚集索引状况下 和 聚集索引状况下的 常量绑定和变量绑定做了测试。会发在这边是因为聚集索引的变量绑定的执行计划，和非聚集索引的变量绑定的执行计划不一样。use northwindCREATE INDEX idx_orderdate_orders ON dbo.Orders(OrderDate)DBCC FREEPROCCACHE SELECT *
阅读全文
摘要：--1. 查看数据库northwind 是否启用 全文索引 SELECT * FROM sys.databasesUSE NorthWind--2.创建全文目录CREATE FULLTEXT CATALOG [employee_fulltext]WITH ACCENT_SENSITIVITY = OFFAS DEFAULT--3. 指定唯一索引CREATE FULLTEXT INDEX ON [dbo].[Employees] KEY INDEX [PK_Employees] ON ([employee_fulltext]) WITH (CHANGE_TRACKING AUTO)--4...
阅读全文
摘要：这问题今天纠结了我一天了。下面的代码是网上转载来的create partition function PF_Orders_OrderDateRange(datetime)asrange right for values ('1997-01-01','1998-01-01','1999-01-01')go-- 创建分区方案create partition scheme PS_Ordersaspartition PF_Orders_OrderDateRangeto ([primary], [primary], [primary], [primary]
阅读全文
摘要：SQL Server 备份中又很多术语，让阅读联机文档的读者感到很蛋疼，很纠结，不知道那个是那个。介质集/媒体集（联机文档中尽然有2中叫法，让俺们初学者很蛋疼）：备份介质有序的集合。备份介质/备份媒体：应该是备份设备的一个统称。物理备份设备：这个比较简单，就是大家常用的 disk='c:\xxx.bak'。逻辑备份设备：通过sp_adddumpdevice 定义，使用方法为 disk = xxx。介质簇/媒体簇：我也不知道干嘛用的介质标头：如果是一样的标头的备份那么和起来就是一个介质集。备份集：成功的备份操作将向介质集中添加一个“备份集”。下面结合一下backup 语句：BAC
阅读全文
摘要：表连接的方式如join,semi-join,outer-join,anti-join；表连接的实现方式如nested loop,merge,hash.本文简单的介绍表连接的方式join,semi-join,outer-join,anti-join和适用情景。假设２个数据源(row source)．Emp(id pk,ename,deptno) Dept(deptno pk,dname)如下是joinselect ename,dname from emp,dept where emp.deptno=dname.deptno;２个数据源键值一一比较，返回相互匹配的记录集for example: n
阅读全文
|
