2012年6月 的存档
2012六月25

ViewStateException: Invalid viewstate.

开发遇到的问题 评论关闭

错误明细:
详细错误:
System.Web.UI.ViewStateException: Invalid viewstate. Client IP: 192.168.21.184 Port: 1418 Referer: http://192.168.16.55:888/Increment/PersonalCustomer.aspx?type=developcall&MenuID=508&menuname=发展部客户跟进 Path: /Increment/PersonalCustomer.aspx User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727) ViewState: /wEPDwUJMzIwOTI4NzM3D2QWAgIBD2QWAgIBDw8WAh4EVGV4dAVJ57O757uf5bey6Ieq5Yqo6YCa55+l5oqA5pyv6YOo5aSE55CGLOivt+eojeWQjuiuv+mXriEgIC0tMjAxMi0wNi0yNSAwODoyOGRkZP1AV1Xh2GYIxvsOtpTpoN+mHmQzbqafkrv3eZ/waeQN,/wEPDwUJMzIwOTI4NzM3D2QWAgIBD2QWAgIBDw8WAh4EVGV4dAVJ57O757uf5bey6Ieq5Yqo6YCa55+l5oqA5pyv6YOo5aSE55CGLOivt+eojeWQjuiuv+mXriEgIC0tMjAxMi0wNi0yNSAwODoyOGRkZP1AV1Xh2GYIxvsOtpTpoN+mHmQzbqafkrv3eZ/waeQN,/wEPDwUKMTg1MDQyMjI5Nw9kFgICAw9kFgICAw9kFgJmD2QWDgIHDxBkEBUHDOivt+mAieaLqS4uLg/mlrDlrqLmiLfotYTmlpkKQeexu+WuouaItwpC57G75a6i5oi3CkPnsbvlrqLmiLcKROexu+WuouaItwzlj5bmtojorqLljZUVBwAOYXV0b2Rpc3RyaWJ1dGUJc3Ryb25nYnV5CWludGVuZGJ1eQh0aGlua2J1eQdpc3ZhbGlkBmNhbmNlbBQrAwdnZ2dnZ2dnZGQCCQ8QZGQWAGQCDQ8QZBAVCwotLeaJgOaciS0tCeaXoOS6uuaOpQnp… —> System.FormatException: The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or a non-white space character among the padding characters.

原因:页面中含有多个_VIEWSTATE引起,导致此错误。
解决方法:禁用某一页面的viewstate.
 方法:1)page上加EnableViewState="false" 2)去掉form的runat=server

2012六月22

合并复制sch-m与sch-s冲突严重

sql server 评论关闭

在合并复制中,sqlserver会不断的同步架构,加上sch-m锁,即使架构没有更改,这就业务系统查询所用的sch-s冲突。为解决此问题,可暂停架构的同步,不过引起另一问题时,更新的存储过程等不会自动分发下去了,变通之道,在同步之前,开启架构复制同步,同步完成后,关闭。

—-开启架构同步
EXEC sp_changemergepublication   @publication = 'BRM-NewCOPY',   @property = N'replicate_ddl', @value = 1;
GO

—-更新存储过程…

—-关闭架构同步
EXEC sp_changemergepublication   @publication = 'BRM-NewCOPY',   @property = N'replicate_ddl', @value = 0;
GO

参考:http://msdn.microsoft.com/en-us/library/ms152562%28v=sql.90%29.aspx

By default, the following schema changes made at a Publisher running SQL Server 2005 are replicated to all SQL Server Subscribers:

  • ALTER TABLE
  • ALTER VIEW
  • ALTER PROCEDURE
  • ALTER FUNCTION
  • ALTER TRIGGER
2012六月18

全局唯一标识符

sql server 评论关闭

尽管 IDENTITY 属性在一个表内自动进行行编号,但具有各自标识符列的各个表可以生成相同的值。这是因为 IDENTITY 属性仅在使用它的表上保证是唯一的。如果应用程序必须生成在整个数据库或世界各地所有网络计算机的所有数据库中均为唯一的标识符列,请使用 ROWGUIDCOL 属性、uniqueidentifier 数据类型和 NEWID 函数。

使用 ROWGUIDCOL 属性定义 GUID 列时,请注意下列几点:

  • 一个表只能有一个 ROWGUIDCOL 列,且必须通过使用 uniqueidentifier 数据类型定义该列。
     
  • 数据库引擎 不会自动为该列生成值。若要插入全局唯一值,请为该列创建 DEFAULT 定义来使用 NEWID 函数生成全局唯一值。
     
  • 在设置 ROWGUIDCOL 属性后,通过使用 $ROWGUID 关键字可以在选择列表中引用该列。这与通过使用 $IDENTITY 关键字可以引用 IDENTITY 列的方法类似。
     
  • OBJECTPROPERTY 函数可用于确定一个表是否具有 ROWGUIDCOL 列,COLUMNPROPERTY 函数可用于确定 ROWGUIDCOL 列的名称。
     
  • 由于 ROWGUIDCOL 属性不强制唯一性,因此应使用 UNIQUE 约束来保证插入到 ROWGUIDCOL 列中的值是唯一的。
     

在创建表时创建新的标识符列

2012六月16

DataReader是否自动关闭

ASP.NET 评论关闭

1,若是用于数据绑定,如gridview,因使用MoveNext方法读取数据,该方法在结束后调用DataReader.Close()方法,故会
关闭datareader,是否关闭connection呢,要看执行时是否传了CloseConnection进去,若传了,则在DataReader.Close()关闭gridview的同时,会自动关闭connection
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
2,其它情况,因不能保证完全遍历完数据,所以最好要手工执行DataReader.Close()方法

2012六月14

deadlock

sql server 评论关闭

SQL Server selects the deadlock victim based on the following criteria:
1. Deadlock priority – the assigned DEADLOCK_PRIORITY of a given session determines
the relative importance of it completing its transactions, if that session is
involved in a deadlock. The session with the lowest priority will always be chosen as
the deadlock victim. Deadlock priority is covered in more detail later in this chapter.
2. Rollback cost – if two or more sessions involved in a deadlock have the same
deadlock priority, then SQL Server will choose as the deadlock victim the session that
has lowest estimated cost to roll back.
3,when occurs,it selects one of the participants as a victim, cancels that spid’s current batch, and rolls backs his transaction in order to let the other spids continue with their work.  The deadlock victim will get a 1205 error:  
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There are circumstances (for example, a critical report that performs a long running
SELECT that must complete even if it is the ideal deadlock victim) where it may be
preferable to specify which process will be chosen as the deadlock victim in the event
of a deadlock, rather than let SQL Server decide based purely on the cost of rollback. As
demonstrated in Listing 7.18, SQL Server offers the ability to set, at the session or batch
level, a deadlock priority using the SET DEADLOCK PRIORITY option.

– Set a Low deadlock priority
SET DEADLOCK_PRIORITY LOW ;
GO
– Set a High deadlock priority
SET DEADLOCK_PRIORITY HIGH ;
GO
– Set a numeric deadlock priority
SET DEADLOCK_PRIORITY 2 ;-10到10

SQL Server 2005 and 2008 however, have three named deadlock priorities; Low, Normal, and High, as well
as a numeric range from -10 to +10, for fine-tuning the deadlock priority of different
operations.
 

2012六月12

RAID configurations

sql server 评论关闭

1,RAID 0RAID 0, most commonly known as striping, provides improved I/O rates (in terms of IOPS) by striping the data across multiple drives, allowing the read and write operations to be shared amongst the drives inside the array.This level of RAID provides the best performance for both read and write operations, butprovides no redundancy or protection against data loss. In the event of a single disk failure in the array, all of the data is lost.
one task A,have a+b, for raid0:risk 1:a,risk 2:b ,so:
n disks: n(read+write)

2,RAID 1, most commonly known as mirroring, provides protection against the loss of
data from a single disk by mirroring the writes to a second disk but doesn't provide added write performanceto the system, since the maximum throughput for the array is limited to the I/O capacity
of a single disk
one task A,have a+b, for raid1: write: risk 1:a+b,risk 2:a+b ,so:
n disks: (n read + n/2 write)

3,RAID 10
RAID 10, or RAID 1+0, is a nested RAID level known as a "striped pair of mirrors." It
provides redundancy by first mirroring each disk, using RAID 1, and then striping those
mirrored disks, with RAID 0, to improve performance.
one task A,have a+b, for raid10: write: risk 1:a,risk11:a, risk 2:b ,risk:22 b
so:
n disks: (n read + n/2 write)

4,RAID 01
RAID 01 or RAID 0+1, is a nested RAID level also known as "mirrored pairs of striped
disks." In a RAID 0+1 configuration, the nested levels are the opposite of the RAID 1+0,
with the disks first being striped in a RAID 0 and then mirrored using RAID 1. However,
this type of configuration only allows for a single disk loss from one side of the array,
since a single disk failure in a RAID 0 array causes that array to fail. A loss of a single disk
in both of the RAID 0 striped arrays would result in total data loss.

5,RAID 5 (and 6)
RAID 5 is commonly known as "striping with parity;" the data is striped across multiples
disks, as per RAID 0, but parity data is stored in order to provide protection from single
disk failure. The minimum number of disks required for a RAID 5 array is three. The
parity blocks containing the parity data are staggered across the stripes inside the array
n disks: ((n-1) (read +write)

Raid 0 –每个磁盘的I/O计算= (读+写) /磁盘个数

  Raid 1 –每个磁盘的I/O计算= [读+(2*写)]/2

  Raid 5 –每个磁盘的I/O计算= [读+(4*写)]/磁盘个数

  Raid 10 –每个磁盘的I/O计算= [读+(2*写)]/磁盘个数

由上知,raid5与raid10比较,raid5对单块磁盘的I/O要求较高,常采用raid10
虽然raid5奇偶性是实现容错的一种不错的方式。但是从磁盘写入来说代价高昂。也就是说对于每一个IOP写请求,RAID5需要4个IOPS。

    为什么需要这么高写入代价的过程如下:

  •     读取原始数据(1 iop)
  •     读取当前奇偶数据(1 iop)
  •     比较当前数据和新写入请求
  •     基于数据差异计算新的奇偶值
  •     写入新数据(1 iop)
  •     写入新的奇偶值(1 iop)


6,choose what?
Data files:
 1,depends heavily on the read-to-write ratio for the .SQL Server tracks the I/O usage of the database files for an instance and makes this information available in the sys.dm_io_virtual_
file_stats Dynamic Management Function. While this information can be used
to determine if your overall workload is heavier for reads vs. writes.
For a database that is primarily read-only, RAID 5 or RAID 6 can offer good read performance.
2,RAID 5 or 6 arrays are commonly used for data warehouses, or for storing data where write latency doesn't impact overall system performance.
3,For OLTP implementations of heavy-write databases, RAID 1+0 provides the best performance

Log files:
    Since the transaction log is written to sequentially, RAID 1 can be used in most situations.the transaction log for each database should be located on dedicated physical disks

tempdb:As a general rule, the tempdb database files should be physically separate from the user
data files and transaction log files, on a dedicated disk array. Since tempdb is a writeheavy
database, RAID 1 or RAID 1+0 are usually the configurations best able to support
the concurrent workload of tempdb.since tempdb is used for temporary storage only, Solid State Disks and even RamDisks can be used to significantly improve the I/O characteristics of the tempdb database

 

http://blog.tianya.cn/blogger/post_show.asp?idWriter=0&Key=0&BlogID=854352&PostID=20759437

2012六月11

跟踪状态

sql server 评论关闭

显示当前打开的状态
DBCC TRACESTATUS()

–在当前连接打开3205
DBCC TRACEON (3205)
–全局打开1222
DBCC TRACEON (3205-1)

2012六月10

how to analysis

sql server 评论关闭
When looking at the wait statistics being tracked by SQL Server, it's important that these
wait types are eliminated from the analysis, allowing the more problematic waits in the
system to be identified. One of the things I do as a part of tracking wait information is to
maintain a script that filters out the non-problematic wait types, as shown in Listing 1.2.'

Troubleshooting SQL Server
A Guide for the Accidental DBA

--1,wait types,if for i/o,then sys.dm_io_virtual_file_stats
--2,sqlserver perfmon counts view

SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / NULLIF(SUM(signal_wait_time_ms) OVER ( ),0)
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

CXPACKET
Often indicates nothing more than that certain queries are executing with parallelism;
CXPACKET waits in the server are not an immediate sign of problems

SOS_SCHEDULER_YIELD
This may indicate that the server is under CPU press

THREADPOOL
requiring an increase in the number of
CPUs in the server, to handle a highly concurrent workload, or it can be a sign of
blocking

LCK_*
These wait types signify that blocking is occurring in the system and that sessions
have had to wait to acquire a lock of a specific typ
This problem can be investigated further using the information
in the sys.dm_db_index_operational_stats

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG
These waits are commonly associated with disk I/O bottlenecks, though the root
cause of the problem may be,PAGEIOLATCH_* waits are
specifically associated with delays in being able to read or write data from the database
files. WRITELOG waits are related to issues with writing to log files.
These waits
should be evaluated in conjunction with the virtual file statistics as well as Physical
Disk performance counters

PAGELATCH_*
A lot of times
PAGELATCH_* waits are associated with allocation contention issues. One of
the best-known allocations issues associated with PAGELATCH_* waits occurs in
tempdb when the a large number of objects are being created and destroyed in
tempdb and the system experiences contention on the Shared Global Allocation
Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the
tempdb database.

LATCH_*
Determining the
specific latch class that has the most accumulated wait time associated with it can
be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO
This wait is often incorrectly attributed to a network bottleneck,In fact, the most
common cause of this wait is a client application that is performing row-by-row
processing of the data being streamed from SQL Server as a result set (client accepts
one row, processes, accepts next row, and so on). Correcting this wait type generally
requires changing the client-side code so that it reads the result set as fast as possible,
and then performs processing

After fixing any problem in the server, in order to validate that the problem has indeed
been fixed, the wait statistics being tracked by the server can be reset using the code in
Listing 1.3.

DBCC SQLPERF('sys.dm_os_wait_stats', clear)

--------------disk I/O bottleneck.-----------
will provide cumulative physical I/O statistics, the number of reads
and writes on each data file, and the number of reads and writes on each log file, for
the various databases in the instance, from which can be calculated the ratio of reads to
writes. This also shows the number of I/O stalls and the stall time associated with the
requests, which is the total amount of time sessions have waited for I/O to be completed
on the file.
whether heavy-read or heavy-write, and at the average latency associated with the I/O, as this will
direct further investigation and possible solutions.

SELECT DB_NAME(vfs.database_id) AS database_name ,

vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC

SELECT * FROM sys.dm_os_performance_counters

DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END ;
-- Capture the first counter set
SELECT CAST(1 AS INT) AS collection_instance ,
[OBJECT_NAME] ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page life expectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time (ms)'
)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Re-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Compilations/sec'
)
-- Wait on Second between data collection
WAITFOR DELAY '00:00:01'
-- Capture the second counter set
SELECT CAST(2 AS INT) AS collection_instance ,
OBJECT_NAME ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_second
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page life expectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time (ms)'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Re-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQL Compilations/sec'
)
-- Calculate the cumulative counter values
SELECT i.OBJECT_NAME ,
i.counter_name ,
i.instance_name ,
CASE WHEN i.cntr_type = 272696576
THEN s.cntr_value - i.cntr_value
WHEN i.cntr_type = 65792 THEN s.cntr_value
END AS cntr_value
FROM #perf_counters_init AS i
JOIN #perf_counters_second AS s
ON i.collection_instance + 1 = s.collection_instance
AND i.OBJECT_NAME = s.OBJECT_NAME
AND i.counter_name = s.counter_name
AND i.instance_name = s.instance_name
ORDER BY OBJECT_NAME
-- Cleanup tables
DROP TABLE #perf_counters_init
DROP TABLE #perf_counters_second

The two Access Methods counters provide information about the ways that tables
are being accessed in the database. The most important one is the Full Scans/sec
counter, which can give us an idea of the number of index and table scans that are
occurring in the SYSTEM

In general, I want the number
of Index Searches/sec to be higher than the number of Full Scans/sec by a factor
of 800–1000. If the number of Full Scans/sec is too high, refer to Chapter 5, Missing
Indexes to determine if there are missing indexes in the database, resulting in excess
I/O operations.

Page Life Expectancy (PLE) which is the number of seconds a page will remain
in the data cache
the question VALUE <= (max server memory/4)*300s,
Writes/sec, which are page flushes from the buffer cache outside of the normal CHECKPOINT
process, then the server is most likely experiencing data cache memory pressure,
which will also increase the disk I/O being performed by the SQL SERVER,At this point
the Access Methods counters should be investigated to determine if excessive table or
index scans are being performed on the SQL SERVER

The General Statistics\Processes Blocked, Locks\Lock Waits/sec,
If these counters return a value other
than zero, over repeated collections of the data, then blocking is actively occurring in one
of the databases , Blocking should be used to
troubleshoot the problems further
 sp_configure ('show advanced options')

 The higher the number of SQL Compilations/
sec in relation to the Batch Requests/sec, the more likely the SQL Server is
experiencing an ad hoc workload that is not making optimal using of plan caching. The
higher the number of SQL Re-Compilations/sec in relation to the Batch Requests/
sec, the more likely it is that there is an inefficiency in the code design that is forcing
a recompile of the code being executed in the SQL Server. In either case, investigation
of the Plan Cache
 show advanced options

 The Memory Manager\Memory Grants Pending performance counter provides
information about the number of processes waiting on a workspace memory grant in
the instance.If this counter has a high value,there may be query inefficiencies in the instance that are causing excessive
memory grant requirements, for example, large sorts or hashes that can be resolved by
tuning the indexing or queries being executed
2012六月8

死锁原因的排查

sql server 评论关闭

1,打开跟踪,写入日志
DBCC TRACEON (3605,1222,-1) –3605写入errorlog,1222死锁
DBCC TRACEON(1222,-1) /若在启动时,加-T 1222
同时,可开profile中的Deadlock graph跟踪,以图形化
2,分析安装目录下生成的日志
   1)确定死锁的资源, 据对象不同查看方式不同。
DBCC TRACEON(3604)

DBCC PAGE(8,1,96,1)
DBCC TRACEOFF(3604)

死锁产生的前提:双方互占有了对方所需求的资源,若资源并不必要,可过滤掉。
资源不必要:1)扫描了对方的资源,扫描过的就会加锁,避免被扫描到,如加索引等。
3,死锁处理方法:
 1)从性能出发,优化sql
 2)从业务逻辑出发,看是否可去掉对死锁资料的关联。
 3)若还是不能解决,死锁是正常情况,避免不了,但可避免输出1205错误信息给客户端,方法就是加上try catch,可以等一会儿再重新执行。
如:

/*避免死锁显示给客户端 */
DECLARE @retries INT ;
SET @retries = 4 ;
WHILE ( @retries > 0 )
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION ;
            -- place sql code here
            SET @retries = 0 ;
            COMMIT TRANSACTION ;
        END TRY
        BEGIN CATCH
            -- Error is a deadlock
            IF ( ERROR_NUMBER() = 1205 )
                SET @retries = @retries - 1 ;
                -- Error is not a deadlock
            ELSE
                BEGIN
                    DECLARE @ErrorMessage NVARCHAR(4000) ;
                    DECLARE @ErrorSeverity INT ;
                    DECLARE @ErrorState INT ;
                    SELECT  @ErrorMessage = ERROR_MESSAGE() ,
                            @ErrorSeverity = ERROR_SEVERITY() ,
                            @ErrorState = ERROR_STATE() ;
                    -- Re-Raise the Error that caused the problem
                    RAISERROR (@ErrorMessage, -- Message text.
                    @ErrorSeverity, -- Severity.
                    @ErrorState -- State.
                    ) ;
                    SET @retries = 0 ;
                END
            IF XACT_STATE() <> 0
                ROLLBACK TRANSACTION ;
        END CATCH ;
    END ;
GO

/*死锁模拟
1,建立数据
2,连续两个事务
*/
drop table Employee_Demo_Heap
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Employee_Demo_Heap](
	[EmployeeID] [int] NOT NULL,
	[NationalIDNumber] [nvarchar](15) NOT NULL,
	[ContactID] [int] NOT NULL,
	[LoginID] [nvarchar](256) NOT NULL,
	[ManagerID] [int] NULL,
	[Title] [nvarchar](50) NOT NULL,
	[BirthDate] [datetime] NOT NULL,
	[MaritalStatus] [nchar](1) NOT NULL,
	[Gender] [nchar](1) NOT NULL,
	[HireDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL   DEFAULT (getdate()),
 CONSTRAINT [PK_Employee_EmployeeID_Demo_Heap] PRIMARY KEY nonCLUSTERED
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Employee_ManagerID_Demo_Heap] ON [Employee_Demo_Heap]
(
	[ManagerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Employee_ModifiedDate_Demo_Heap] ON [Employee_Demo_Heap]
(
	[ModifiedDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
insert into Employee_Demo_Heap
select [EmployeeID] ,
	[NationalIDNumber] ,
	[ContactID] ,
	[LoginID] ,
	[ManagerID],
	[Title] ,
	[BirthDate] ,
	[MaritalStatus] ,
	[Gender] ,
	[HireDate] ,
	[ModifiedDate] from HumanResources.Employee
go

现在就用下面这组脚本模拟出一个死锁来。在一个连接里,运行下面的语句。反复开启事务。在这个事务里,先修改一条NationalIDNumber=‘480951955’的记录,然后再把它查询出来。做完以后,提交事务。
set nocount on
go
while 1=1
begin
begin tran
update dbo.Employee_Demo_Heap
set BirthDate = getdate()
where NationalIDNumber = '480951955'
select * from dbo.Employee_Demo_Heap
where NationalIDNumber = '480951955'
commit tran
end
	在另外一个连接里,也运行这些语句。唯一的差别是这次修改和查询的是另一条NationalIDNumber = ‘407505660’的记录。
set nocount on
go
while 1=1
begin
begin tran
update dbo.Employee_Demo_Heap
set BirthDate = getdate()
where NationalIDNumber = '407505660'
select * from dbo.Employee_Demo_Heap
where NationalIDNumber = '407505660'
commit tran
end
2012六月8

自增:用SCOPE_IDENTITY()替换@@Identity

sql server 评论关闭

SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。
 

若有触发器,且触发器有插入其它表,则@@IDENTITY返回的是其它表的自增值,而不是自己所想要的,故要
用SCOPE_IDENTITY(),IDENT_CURRENT有并发问题,不考虑。

@@identity不受并发影响。因为他只受当前会话限制。
但可能会受到当前会话其它作用域的限制。
比如ta上建有insert触发器,触发器内容为在ta上插入时,同时向tb插入。
那么这时@@identity就会有误,它取到的不是ta上的新增标识值,而是tb的。
或者存储过程嵌套也可能产生问题。
如果希望受当前会话,并且只取当前作用域的值的话那么使用scope_identity()函数。

 

如果插入的表未有插入触发器插入其它表记录生成新的id列,
用@@identity无问题,各有各的连接,互不相干.
用SCOPE_IDENTITY 回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值
用scope_identity也可

IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值
ident_current会受并发影响.

2012六月5

存储过程在程序慢而在Management Studio快的原因

sql server 评论关闭

1,存储过程在程序慢而在Management Studio快,分析查询计划,发现生成了两个执行计划,说明二者用的不是同一个查询计划,因参数嗅探问题引发此种情况。
2,产生不同执行计划原因是:引起执行计划重新编译的原因之一是上下文环境发生改变,SET开关。这些开关会影响语句执行的行为,甚至带来不同的结果,而sqlserver要另生成新的编译计划。而在Management Studio中,默认打开了SET ARITHABORT ON 在查询执行过程中发生溢出或被零除错误时,终止查询,而程序中是不打开的,不同的SET开关,造成不同编译计划的生成,而不是重用。
最终设置:看存储过程用的什么set,将Manage Studio工具-选项-查询执行-高级 打开或关掉工具
3,故要重用程序所生成的执行计划,方式是:1)确定SET设置 2)在当前中设置上下文环境

--查找SET开关
方法1:
SELECT sql,setopts
FROM sys.syscacheobjects
WHERE OBJECT_NAME(objid,DB_ID()) = 'frmUser_sel'

方法2:
SELECT plan_handle,usecounts,pvt.set_options, pvt.sql_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value ,usecounts
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) qt
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan'
    AND  OBJECT_NAME(objectid,dbid) = 'frmUser_sel' AND dbid = DB_ID()
    ) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO

--将获取的值传入,获取具体SET项
declare @set_options INT
SET @set_options= 251
 
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

–查看当前option select @@option 参考:Set Options

4,上述说明了相同的存储过程参数,但产生不同的执行计划的原因,但关于速度不一样呢,原因是发生了参数嗅探,解决参数嗅探方法有:1)重新编译存储过程 sp_recompile 'sp' 2)在关键语句加:option(recompile) 3)指定值OPTION (OPTIMIZE FOR (@pid = 897))

2012六月1

ISNULL的陷阱

sql server 评论关闭

ISNULL:
check_expression

将被检查是否为 NULL 的表达式。check_expression 可以为任何类型。

replacement_value

当 check_expression 为 NULL 时要返回的表达式。replacement_value 必须是可以隐式转换为 check_expresssion 类型的类型。

ISNULL:若前一个为NULL,时,会将后一个按前一个数据类型进行转换!
测试:

DECLARE @s VARCHAR(10),@result VARCHAR(50)
           SET @result = REPLICATE(1,50)
           SELECT LEN(@result)
           SELECT LEN(ISNULL(@s,@result))