SQL Server DBCCÃüÁî
¡ô1.DBCC CacheStats £ºÏÔʾ´æÔÚÓÚµ±Ç° buffer Cache ÖеĶÔÏóµÄÐÅÏ¢£¬ÀýÈç £ºhitrates ±àÒëµÄ¶ÔÏóºÍÖ´Ðмƻ®
DBCC CACHESTATS
¡¡¡¡´ÓÕâ¸öÃüÁî¿ÉÒԵõ½Ò»Ð©¹Ø¼üµÄͳ¼ÆÐÅÏ¢£º
¡¡¡¡Hit Ratio£ºÏÔÊ¾ÌØ¶¨¶ÔÏó¿ÉÒÔÔÚSql ServerµÄ»º´æÖб»ÃüÖеİٷֱȣ¬Õâ¸öÊýÖµÔ½´ó£¬Ô½ºÃ
¡¡¡¡Object Count£ºÏÔÊ¾ÌØ¶¨ÀàÐ͵ĶÔÏóÔÚsql serverµÄ»º´æÖб»ÃüÖеÄ×ÜÊý
¡¡¡¡Avg.Cost:sql serverÓÃÓÚ²âÁ¿±àÒëÒ»¸öÖ´Ðмƻ®ËùÐèµÄʱ¼ä£¬ÒÔ¼°Õâ¸ö¼Æ»®ËùÐèµÄÄÚ´æ¡£¸ù¾ÝÕâ¸öÖµ£¬¿ÉÒÔ¾ö¶¨Ö´Ðмƻ®ÊÇ·ñÓ¦¸Ã¼ÓÔØÔÚ»º´æÖС£
¡¡¡¡Avg.Pages£º²âÁ¿ÔÚ»º´æÖеĶÔÏóʹÓÃ8KÒ³µÄƽ¾ù×ÜÊý
¡¡¡¡LW Ojbect Count£¬LW Avg Cost£¬WL Avg Stay£¬LW Ave Use£ºÕâЩÁеÄÖµ±íÃ÷ÓжàÉÙÌØ¶¨µÄ¶ÔÏóÒѾ±»Ð´½ø½ø³Ì´Ó»º´æ×ÜÒÆ×ß¡£ÕâЩÊýÖµÔ½µÍ£¬Ô½ºÃ¡£
¡ô2.DBCC DROPCLEANBUFFERS£º´Ó»º³å³ØÖÐɾ³ýËùÓУ¬Çå³ý»º³åÇø¡£
ÔÚ½øÐвâÊÔʱ£¬Ê¹ÓÃÕâ¸öÃüÁî¿ÉÒÔ´Ósql server’sµÄÊý¾Ý»º´ædata catch(buffer)Çå³ýËùÓеIJâÊÔÊý¾Ý£¬ÒÔ±£Ö¤²âÊԵĹ«ÕýÐÔ¡£ÐèҪעÒâµÄÊÇÕâ¸öÃüÁîÖ»ÒÆ×߸ɾ»µÄ»º´æ£¬²»ÒÆ×ßÔ໺´æ¡£ÓÉÓÚÕâ¸öÔÒò£¬ÔÚÖ´ÐÐÕâ¸öÃüÁîǰ£¬Ó¦¸ÃÏÈÖ´ÐÐCheckPoint£¬½«ËùÓÐÔàµÄ»º´æÐ´Èë´ÅÅÌ£¬ÕâÑùÔÚÔËÐÐDBCC RROPCLEANBUFFERS ʱ£¬¿ÉÒÔ±£Ö¤ËùÓеÄÊý¾Ý»º´æ±»ÇåÀí£¬¶ø²»ÊÇÆäÖеÄÒ»²¿·Ö¡£
¡ô3.DBCC ErrorLog £ºÈç¹ûºÜÉÙÖØÆðmssqlserver·þÎñ£¬ÄÇô·þÎñÆ÷µÄÈÕÖ¾»áÔö³¤µÃºÜ¿ì£¬¶øÇÒ´ò¿ªºÍ²é¿´ÈÕÖ¾µÄËÙ¶ÈÒ²»áºÜÂý¡£Ê¹ÓÃÕâ¸öÃüÁ¿ÉÒԽضϵ±Ç°µÄ·þÎñÆ÷ÈÕÖ¾£¬Ö÷ÒªÊÇÉú³ÉÒ»¸öеÄÈÕÖ¾¡£¿ÉÒÔ¿¼ÂÇÉèÖÃÒ»¸öµ÷¶ÈÈÎÎñ£¬Ã¿ÖÜÖ´ÐÐÕâ¸öÃüÁî×Ô¶¯½Ø¶Ï·þÎñÆ÷ÈÕÖ¾¡£Ê¹Óô洢¹ý³Ìsp_cycle_errorlogÒ²¿ÉÒԴﵽͬÑùµÄÄ¿µÄ¡£
¡ô4.DBCC FLUSHPROCINDB:ÓÃÓÚÇåÀíÒ»¸öÊý¾Ý¿âʵÀýÖÐÖ¸¶¨Êý¾Ý¿âµÄ´æ´¢¹ý³ÌʹÓõĻº´æ¡£Êý¾Ý¿âµÄIDÊDZØÊä²ÎÊý
ÔÚ²âÊÔʱ±£Ö¤ÒÔǰµÄ´æ´¢¹ý³Ì¼Æ»®²»»á¶Ô²âÊÔ½á¹ûÔì³É¸ºÃæÓ°Ï죬¿ÉÒÔʹÓÃÕâ¸ö´æ´¢¹ý³Ì¡£
¡¡¡¡Àý£º
DECLARE @intDBID INTEGER SET @intDBID = (select dbid from master.dbo.sysdatabases where name = 'database_name')
DBCC FLUSHPROCINDB (@intDBID)
¡ô5.DBCC FREEPROCCACHE£ºÓÃÓÚÇåÀíËùÓÐÊý¾Ý¿âµÄ¹ý³Ì¸ßËÙ»º´æ¡£
ÀýÈ磬ÊͷŹý³Ì¸ßËÙ»º´æ½«µ¼ÖÂÖØÐ±àÒëijЩ²¿·Ö(ÀýÈçÌØ±ð SQL Óï¾ä)£¬¶ø²»ÊÇ´Ó¸ßËÙ»º´æÖÐ¶ÔÆäÔÙʹÓÃ
Ïà¹ØÎĵµ£º
DECLARE @temp TABLE(
id INT,
[name] VARCHAR(50),
class VARCHAR(50)
)
INSERT INTO @temp
SELECT 1,'a','A'
UNION ALL SELECT 2,'b','C'
UNION ALL SELECT 3,'c','B'
UNION ALL SELECT 4,'d','C'
UNION ALL SELECT 5,'e','B'
UNION ALL SELECT 6,'f','A'
SELECT * from @temp AS _temp WHERE [name] IN
(
......
SQLµÄ»ù±¾¶ÔÏóÖ÷ÒªÓг£Á¿£¬±íʾ·û£¬·Ö¸ô·û£¬±£Áô¹Ø¼ü×Ö¡£
1¡¢³£Á¿
³£Á¿ÊÇÒ»¸ö°üº¬ÎÄ×ÖÓëÊý×Ö£¬Ê®Áù½øÖÆ»òÊý×Ö³£Á¿¡£Ò»¸ö×Ö·û´®³£Á¿°üº¬µ¥ÒýºÅ('')»òË«ÒýºÅ("")×Ö·û¼¯ÖеÄÒ»¸ö»ò¶à¸ö×Ö·û¡£
Èç¹ûÏëÔÚµ¥ÒýºÅ·Ö¸ôµÄ×Ö·û´®ÖÐÓõ½µ¥¶ÀµÄÒýºÅ£¬¿ÉÒÔÔÚÕâ¸ö×Ö·ûÖÐÓû§Á¬ÐøµÄµ¥ÒýºÅ£¨¼´ÓÃÁ ......
Ò»¡¢ÎÊÌâ
select UserID,LastName,FirstName,UserName from SYSUser
UserID LastName FirstName UserName
------------------------------------------------------
1 A B C
2 &nb ......
The following tables describe certain SQL
limits. Adhering to the most restrictive case can help the programmer
design application programs that are easily portable.
Table 7. Identifier Length Limits
Description
Limit in Bytes
Longest authorization
name (can only be single-byte characters) ......