ʹÓà SQL Server Profiler µÄ實Àý
ʹÓà SQL Server ʼþ̽²éÆ÷µÄ·½°¸
ʹÓà SQL Server ProfilerµÄµÚÒ»²½ÊÇÈ·¶¨¼àÊÓ SQL Server ʵÀýµÄÔÒò¡£±¾Ö÷ÌâÌÖÂÛʹÓà SQL Server ProfilerÊÕ¼¯¸ú×ÙÐÅÏ¢µÄµäÐÍ·½°¸¡£
ʹÓà SQL Server ProfilerµÄµäÐÍ·½°¸°üÀ¨£º
²éÕÒÖ´ÐÐÇé¿ö×î²îµÄ²éѯ¡£
ÀýÈ磬¿ÉÒÔ´´½¨Ò»¸ö²¶»ñÓë TSQL ºÍ Stored Procedure ʼþÀࣨRPC:Completed ºÍ SQL:BatchCompleted£©Ïà¹ØµÄʼþµÄ¸ú×Ù¡£Ôڴ˸ú×ÙÖаüÀ¨ËùÓÐÊý¾ÝÁУ¬°´ Duration ·Ö×é²¢Ö¸¶¨Ê¼þ×¼Ôò¡£ÀýÈ磬Èç¹ûÖ¸¶¨Ê¼þµÄ Duration ±ØÐëÖÁÉÙΪ 10000 ΢Ã룬Ôò¿ÉÒÔ´Ó¸ú×ÙÖÐÇå³ý³ÖÐøÊ±¼ä½Ï¶ÌµÄʼþ¡£¿ÉÒÔ¸ù¾ÝÐèÒªÔö´ó Duration µÄ×îСֵ¡£Èç¹ûÏ£ÍûÒ»´Î½ö¼àÊÓÒ»¸öÊý¾Ý¿â£¬ÇëÖ¸¶¨ Database ID ʼþ±ê×¼µÄÖµ¡£
È·¶¨ËÀËøµÄÔÒò¡£
ÀýÈ磬¿ÉÒÔ´´½¨Ò»¸ö²¶»ñÓë TSQL ºÍ Stored Procedure ʼþÀࣨRPC:Starting ºÍ SQL:BatchStarting£©ºÍ Locks ʼþÀࣨDeadlock graph¡¢Lock:Deadlock »ò Lock:Deadlock Chain£©Ïà¹ØµÄʼþµÄ¸ú×Ù¡£Ôڴ˸ú×ÙÖаüÀ¨ËùÓÐÊý¾ÝÁв¢°´ Event Class ·Ö×é¡£Èç¹ûÏ£ÍûÒ»´Î½ö¼àÊÓÒ»¸öÊý¾Ý¿â£¬ÇëÖ¸¶¨ Database ID ʼþ±ê×¼µÄÖµ¡£Èç¹ûÖ¸¶¨ Deadlock graph ʼþÀ࣬SQL Server Profiler½«Éú³ÉͼÐλ¯±íʾÐÎʽµÄËÀËø¡£ÓйØÏêϸÐÅÏ¢£¬Çë²ÎÔÄʹÓà SQL Server Profiler ·ÖÎöËÀËø¡£
ÈôÒª²é¿´ËÀËøÖÐÉæ¼°µÄÁ¬½Ó£¬ÇëÖ´ÐÐÏÂÁвÙ×÷Ö®Ò»£º
´ò¿ª°üº¬²¶»ñµÄÊý¾ÝµÄ¸ú×Ù£¬°´ ClientProcessID ¶ÔÊý¾Ý½øÐзÖ×é²¢Õ¹¿ªËÀËøÖÐÉæ¼°µÄÁ½¸öÁ¬½Ó¡£
½«²¶»ñµÄÊý¾Ý±£´æµ½¸ú×ÙÎļþÖУ¬È»ºóÁ½´Î´ò¿ª´Ë¸ú×ÙÎļþ£¬Ê¹Æä³öÏÖÔÚÁ½¸öµ¥¶ÀµÄ SQL Server Profiler´°¿ÚÖС£°´ ClientProcessID ¶Ô²¶»ñµÄÊý¾Ý½øÐзÖ×飬ȻºóÕ¹¿ªËÀËøÖÐÉæ¼°µÄ¿Í»§¶Ë½ø³Ì ID£»Ã¿¸öËÀËøµÄÁ¬½Ó½«³öÏÖÔÚÒ»¸öµ¥¶ÀµÄ´°¿ÚÖС£Æ½ÆÌÕâÁ½¸ö´°¿ÚÒԲ鿴µ¼ÖÂËÀËøµÄʼþ¡£Èç¹ûÒª½«Ìض¨ËÀËøÍ¼ÐÎÊý¾Ý±£´æµ½Îļþ£¬ÇëÓÒ¼üµ¥»÷ËÀËøÊ¼þ²¢Ñ¡Ôñ“ÌáȡʼþÊý¾Ý”¡£
¼àÊÓ´æ´¢¹ý³ÌÐÔÄÜ¡£
ÀýÈ磬¿ÉÒÔ´´½¨Ò»¸ö²¶»ñÓë Stored Procedure ʼþÀࣨSP:Completed¡¢SP:Starting¡¢SP:StmtCompleted ºÍ SP:StmtStarting£©ºÍ TSQL ʼþÀࣨSQL:BatchStarting ºÍ SQL:BatchCompleted£©Ïà¹ØµÄʼþµÄ¸ú×Ù¡£Ôڴ˸ú×ÙÖаüÀ¨ËùÓбØÒªµÄÊý¾ÝÁв¢°´ ClientProcessID ·Ö×é¡£Èç¹ûÏ£ÍûÒ»´ÎÖ»¼àÊÓÒ»¸öÊý¾Ý¿â£¬ÇëʹÓÃÊý¾Ý¿â ID ɸѡÆ÷Ϊ“Êý¾Ý¿â ID”ʼþ±ê×¼Ö¸¶¨Ò»¸öÖµ¡£Í¬Ñù£¬Èç¹ûÏ£ÍûÒ»´ÎÖ»¼àÊÓÒ»¸ö´æ´¢¹ý³Ì£¬ÇëʹÓöÔÏó ID ɸѡÆ÷Ϊ“¶ÔÏó ID”ʼþ±ê×¼Ö¸¶¨Ò»¸öÖµ¡£
ÉóºË SQL Server
Ïà¹ØÎĵµ£º
ÒÔstring ÀàÐ͵ÄNameΪÀý
ÔÚSQL Óï¾äÀ string sql="Name like'%" + Name.Text + "%';
ÔÚ´æ´¢¹ý³ÌÀ
if @Name!=''
begin
set @strWhere= +' and Name like '''+'%'+@Name+'%'+''''
end
´æ´¢¹ý³ÌÖеÄÒýºÅÊǵ¥ÒýºÅ.
like '''+'%'Öм䲻ÓÃ+Èϲ» ......
ÔÚ×ö±¨±íʱoqlÓï¾äÖÐÓÐʱÐèÒªÓõ½Óû§×Ô¶¨Ò庯Êý£¬µ÷ÓÃÖ®ºó±¨±í±¨´í£º¡° ¶ÔÊý¾Ý¼¯¡°DataQuery¡±Ö´Ðвéѯʧ°Ü¡£ 'fn_GetLevelItemCatName' ²»ÊÇ¿ÉÒÔʶ±ðµÄ º¯ÊýÃû³Æ¡£ ')' ¸½½üÓÐÓï·¨´íÎó¡£ ¡° £¬ÓÚÊÇÎÒ½«oqlÓï¾ä½âÎöºóÄÃsqlÓï¾äµ½sql server 2008ÖÐÈ¥Ö´Ðб¨´í£º 'fn_GetLevelItemCatName' ²»ÊÇ¿ÉÒÔʶ±ðµÄº¯ÊýÃû³Æ¡£ ÕâÖÖÎÊÌ ......
´Ë·½·¨ÊÇ´Óһλǰ±²ÄÇÀïѧÀ´µÄ£¬µ¼Óï¾äºÜ·½±ã£¬Ö»ÐèдÇå³þ±íÃû¾ÍÐС£ÅÂÍüÁË£¬ÔݼÇһϡ££¨sql server 2005ÊÔÑé¹ý£©
µÚÒ»´ÎʹÓõϰ£¬ÐèÒª½¨Á¢ÈçÏ´洢¹ý³Ì¡£´úÂëºÜ³¤£¬Ã»¹ØÏµ£¬Ö±½Ócopy¾ÍÐС£
--------- outputdata ´æ´¢¹ý³Ì
CREATE PROCEDURE dbo.OutputData
@tablename sysname
AS
declare @column va ......
¡¡¡¡Êý¾Ý¿â¿ª·¢ÈËÔ±»òÊý¾Ý¿â¹ÜÀíÔ±(DBA)ΪÁË·¢²¼Êý¾Ý¿â»ò±¸·ÝÊý¾Ý¿â¶ÔÏ󣬳£ÐèÒªÉú³ÉT-SQL½Å±¾¡£±ÊÕßÔÚÕâÀï¶Ô³£Ó÷½·¨½øÐÐÁË×ܽᣬ¹©ÅóÓÑÃDzο¼¡£
¡¡¡¡·½·¨Ò»£ºÊ¹ÓÃÆóÒµ¹ÜÀíÆ÷
¡¡¡¡½øÈë“ÆóÒµ¹ÜÀíÆ÷”£¬ÓÒ»÷Êý¾Ý¿â£¬Ñ¡Ôñ“ËùÓÐÈÎÎñ→Éú³ÉSQL½Å±¾”¼´¿É¡£
¡¡¡¡·½·¨ÆÀ¼Û£ºÓŵãÊÇ·½±ã£¬ÇÒ²Ù×÷¼òµ¥¡ ......
OracleÖÐÈçºÎÓÃÒ»ÌõSQL¿ìËÙÉú³É10ÍòÌõ²âÊÔÊý¾Ý
×öÊý¾Ý¿â¿ª·¢»ò¹ÜÀíµÄÈ˾³£Òª´´½¨´óÁ¿µÄ²âÊÔÊý¾Ý£¬¶¯²»¶¯¾ÍÐèÒªÉÏÍòÌõ£¬Èç¹ûÒ»ÌõÒ»ÌõµÄ¼È룬
ÄÇ»áÀË·Ñ´óÁ¿µÄʱ¼ä£¬±¾ÎĽéÉÜÁËOracleÖÐÈçºÎͨ¹ýÒ»ÌõSQL¿ìËÙÉú³É´óÁ¿µÄ²âÊÔÊý¾ÝµÄ·½·¨¡£
²úÉú²âÊÔÊý¾ÝµÄSQLÈçÏ£º
SQL> select rownum as id,
&nb ......