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 Óï¾ä)£¬¶ø²»ÊÇ´Ó¸ßËÙ»º´æÖÐ¶ÔÆäÔÙʹÓÃ
Ïà¹ØÎĵµ£º
SQLµÄ»ù±¾¶ÔÏóÖ÷ÒªÓг£Á¿£¬±íʾ·û£¬·Ö¸ô·û£¬±£Áô¹Ø¼ü×Ö¡£
1¡¢³£Á¿
³£Á¿ÊÇÒ»¸ö°üº¬ÎÄ×ÖÓëÊý×Ö£¬Ê®Áù½øÖÆ»òÊý×Ö³£Á¿¡£Ò»¸ö×Ö·û´®³£Á¿°üº¬µ¥ÒýºÅ('')»òË«ÒýºÅ("")×Ö·û¼¯ÖеÄÒ»¸ö»ò¶à¸ö×Ö·û¡£
Èç¹ûÏëÔÚµ¥ÒýºÅ·Ö¸ôµÄ×Ö·û´®ÖÐÓõ½µ¥¶ÀµÄÒýºÅ£¬¿ÉÒÔÔÚÕâ¸ö×Ö·ûÖÐÓû§Á¬ÐøµÄµ¥ÒýºÅ£¨¼´ÓÃÁ ......
ÉÏÖܽӵ½Ò»¸öÆæ¹ÖµÄbug£¬Ò»¸öÔø¾ÔËÐеúܺõĴ洢¹ý³ÌͻȻ²úÉúÁË´íÎóµÄ½á¹û¡£
¸ºÔðά»¤µÄÐÖµÜÃǺܸºÔðÈεĶԴíÎó½øÐÐÁ˸ú×Ù£¬²¢°Ñ´íÎó¶¨Î»Ò»¸öÈçϵÄÓï¾ä£º
SELECT *
into SomeTable
from A join B on A.id=B.id
join C on A.id=C.id
ËûÃÇ·¢ÏÖ´ÓSomeTable×ö²éѯµÄ ......
Ò»¡¢ÎÊÌâ
select UserID,LastName,FirstName,UserName from SYSUser
UserID LastName FirstName UserName
------------------------------------------------------
1 A B C
2 &nb ......
create PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
set nocount on
--CSDN£ºj9988 copyright:2004.01.05
--V3.1
--ÆÆ½â×Ö½Ú²»ÊÜÏÞÖÆ£¬ÊÊÓÃÓÚSQLSERVER2000´æ´¢¹ý³Ì£¬º¯Êý£¬ÊÓͼ£¬´¥·¢Æ÷
--·¢ÏÖÓÐ´í£¬ÇëE_MAIL£ºCSDNj9988@tom.com
begin tran
declare @objectname1 varchar(100),@orgvarbin varbina ......
±¾ÎĽÚÑ¡×ÔMSDNµÄÎÄÕ¡¶ÎåÖÖÌá¸ß SQL ÐÔÄܵķ½·¨¡·£¬Ìá³öÈçºÎÌá¸ß»ùÓÚSQL ServerÓ¦ÓóÌÐòµÄÔËÐÐЧÂÊ£¬·Ç³£ÖµµÃÍÆ¼ö¡£¶ÔһЩTrafficºÜ¸ßµÄÓ¦ÓÃϵͳ¶øÑÔ£¬ÈçºÎÌá¸ßºÍ¸Ä½øSQLÖ¸ÁÊǷdz£ÖØÒªµÄ£¬Ò²ÊÇÒ»¸öºÜºÃµÄÍ»ÆÆµã¡£
*ÎÄÕÂÖ÷Òª°üÀ¨ÈçÏÂһЩÄÚÈÝ£¨Èç¸ÐÐËȤ£¬ÇëÖ±½Ó·ÃÎÊÏÂÃæµÄURLÔĶÁÍêÕûµÄÖÐÓ¢ÎÄÎĵµ£©£º
1, ´Ó INSERT ·µ ......