Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

¼à¿ØSQL Server2005Êý¾Ý¿âµÄÔËÐÐ×´¿ö

Äܼà¿ØSQL ServerÊý¾Ý¿âÔËÐеÄÇé¿ö£¬¶Ô·þÎñÆ÷À´Ëµ¶¼ÊÇÒ»¼þ·Ç³£ÖØÒªµÄÊ£¬ÏÂÃ濪ʼ½éÉÜ¡£
Microsoft SQL Server 2005 ÌṩÁËһЩ¹¤¾ßÀ´¼à¿ØÊý¾Ý¿â¡£·½·¨Ö®Ò»ÊǶ¯Ì¬¹ÜÀíÊÓͼ¡£¶¯Ì¬¹ÜÀíÊÓͼ (DMV) ºÍ¶¯Ì¬¹ÜÀíº¯Êý (DMF) ·µ»ØµÄ·þÎñÆ÷״̬ÐÅÏ¢¿ÉÓÃÓÚ¼à¿Ø·þÎñÆ÷ʵÀýµÄÔËÐÐ×´¿ö¡¢Õï¶ÏÎÊÌâºÍÓÅ»¯ÐÔÄÜ¡£
³£¹æ·þÎñÆ÷¶¯Ì¬¹ÜÀí¶ÔÏó°üÀ¨£º
dm_db_*£ºÊý¾Ý¿âºÍÊý¾Ý¿â¶ÔÏó
dm_exec_*£ºÖ´ÐÐÓû§´úÂëºÍ¹ØÁªµÄÁ¬½Ó
dm_os_*£ºÄÚ´æ¡¢Ëø¶¨ºÍʱ¼ä°²ÅÅ
dm_tran_*£ºÊÂÎñºÍ¸ôÀë
dm_io_*£ºÍøÂçºÍ´ÅÅ̵ÄÊäÈë/Êä³ö
´Ë²¿·Ö½éÉÜΪ¼à¿Ø SQL Server ÔËÐÐ×´¿ö¶øÕë¶ÔÕâЩ¶¯Ì¬¹ÜÀíÊÓͼºÍº¯ÊýÔËÐеÄһЩ³£Óòéѯ¡£
ժ¼²¿·Ö¾«²ÊSQLÈçÏ£º
ÏÂÃæµÄ²éѯÏÔʾ CPU ƽ¾ùÕ¼ÓÃÂÊ×î¸ßµÄÇ° 50 ¸ö SQL Óï¾ä¡£
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) from sys.dm_exec_sql_text(sql_handle)) AS query_text, *
from sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

ÏÂÃæµÄ²éѯÏÔʾһЩ¿ÉÄÜÕ¼ÓôóÁ¿ CPU ʹÓÃÂʵÄÔËËã·û£¨ÀýÈç ‘%Hash Match%’¡¢‘%Sort%’£©ÒÔÕÒ³ö¿ÉÒɶÔÏó¡£
select *
from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where
cast(query_plan as nvarchar(max)) like '%Sort%'
or cast(query_plan as nvarchar(max)) like '%Hash Match%'

ÔËÐÐÏÂÃæµÄ DMV ²éѯÒԲ鿴 CPU¡¢¼Æ»®³ÌÐòÄÚ´æºÍ»º³å³ØÐÅÏ¢¡£
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
ÏÂÃæµÄʾÀý²éѯÏÔʾÒÑÖØбàÒëµÄÇ° 25 ¸ö´æ´¢¹ý³Ì¡£plan_generation_num ָʾ¸Ã²éѯÒÑÖØбàÒëµÄ´ÎÊý¡£
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stat


Ïà¹ØÎĵµ£º

SQL 2005 tips

USE tempdb
GO
CREATE TABLE AuctionItems
(
  itemid       INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  itemtype     NVARCHAR(30) NOT NULL,
  whenmade     INT&nb ......

SQL²éѯÖظ´Êý¾ÝºÍÇå³ýÖظ´Êý¾Ý


ÓÐÀý±í£ºemp
emp_no   name    age    
    001           Tom      17    
    002       &nb ......

SQLÓ¦ÓÃÈÕÖ¾

UPDATe [Sale_Goods]  SET [Catalog33_ID] = [Catalog].[ID]
from  [Sale_Goods]
LEFT OUTER JOIN [chdy222] ON [Sale_Goods].[Code] = [chdy222].[ID]
LEFT OUTER JOIN [Catalog] ON [chdy222].[ufidaname] = [Catalog].[Name] and [Catalog].[Type]=33
WHERE [Catalog].[ID] IS NOT NULL ......

ÈçºÎ²é¿´SQL Server²¹¶¡°æ±¾

ÈçºÎ²é¿´SQL Server²¹¶¡°æ±¾  [http://www.cnblogs.com/yitian/articles/1285021.html]
1¡¢ÓÃIsql»òÕßSQL²éѯ·ÖÎöÆ÷µÇ¼µ½SQL Server£¬Èç¹ûÊÇÓÃIsql£¬ÇëÔÚcmd´°¿ÚÊäÈëisql -U sa,È»ºóÊäÈëÃÜÂ룬½øÈ룻Èç¹ûÊÇÓÃSQL²éѯ·ÖÎöÆ÷£¬Çë´Ó³ÌÐòÖÐÆô¶¯£¬ÊäÈësaºÍÃÜÂ루Ҳ¿ÉÒÔÓÃwindowsÑéÖ¤£©¡£
2¡¢ÔÚISQLÖÐÊäÈ ......

¼à¿ØÊý¾Ý¿âÐÔÄܵÄSQL»ã×Ü

¼à¿ØÊý¾Ý¿âÐÔÄܵÄSQL»ã×Ü ¡¾http://hi.baidu.com/g%5Fliying/blog/item/89711cfc27b82ff4fc037f80.html¡¿
1. ¼à¿ØÊÂÀýµÄµÈ´ý
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. »Ø¹ö¶ÎµÄÕùÓÃÇé¿ö
se ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ