tempdb¶ÔSQL ServerÊý¾Ý¿âÐÔÄÜÓкÎÓ°Ïì
tempdb¶ÔSQL ServerÊý¾Ý¿âÐÔÄÜÓкÎÓ°Ïì
±¾ÎĹؼü´Ê£ºSQL Server ÍøÂç
Ïà·´Èç¹û·ÃÎʺÜƵ·±,loading¾Í»á¼ÓÖØ,tempdbµÄÐÔÄܾͻá¶ÔÕû¸öDB²úÉúÖØÒªµÄÓ°Ïì.ÓÅ»¯tempdbµÄÐÔÄܱäµÄºÜÖØÒªµÄ,ÓÈÆä¶ÔÓÚ´óÐÍÊý¾Ý¿â.Èç¹ûʹÓÃÁÙʱ±í´¢´æ´óÁ¿µÄÊý¾ÝÇÒƵ·±·ÃÎÊ,¿¼ÂÇÌí¼ÓindexÒÔÔö¼Ó²éѯЧÂÊ.
¡¡ 1.SQL ServerϵͳÊý¾Ý¿â½éÉÜ
¡¡¡¡SQL ServerÓÐËĸöÖØÒªµÄϵͳ¼¶Êý¾Ý¿â:master,model,msdb,tempdb.
¡¡¡¡master:¼Ç¼SQL ServerϵͳµÄËùÓÐϵͳ¼¶ÐÅÏ¢,°üÀ¨ÊµÀý·¶Î§µÄÔªÊý¾Ý,¶Ëµã,Á´½Ó·þÎñÆ÷ºÍϵͳÅäÖÃÉèÖÃ,»¹¼Ç¼ÆäËûÊý¾Ý¿âÊÇ·ñ´æÔÚÒÔ¼°ÕâЩÊý¾ÝÎÊÎļþµÄλÖõȵÈ.Èç¹ûmaster²»¿ÉÓÃ,Êý¾Ý¿â½«²»ÄÜÆô¶¯.
¡¡¡¡model:ÓÃÔÚSQL Server ʵÀýÉÏ´´½¨µÄËùÓÐÊý¾Ý¿âµÄÄ£°å¡£ÒòΪÿ´ÎÆô¶¯ SQL Server ʱ¶¼»á´´½¨ tempdb£¬ËùÒÔ model Êý¾Ý¿â±ØÐëʼÖÕ´æÔÚÓÚ SQL Server ϵͳÖС£
¡¡¡¡msdb:ÓÉSQL Server ´úÀíÓÃÀ´¼Æ»®¾¯±¨ºÍ×÷Òµ¡£
¡¡¡¡tempdb:ÊÇÁ¬½Óµ½ SQL Server ʵÀýµÄËùÓÐÓû§¶¼¿ÉÓõÄÈ«¾Ö×ÊÔ´£¬Ëü±£´æËùÓÐÁÙʱ±í,ÁÙʱ¹¤×÷±í,ÁÙʱ´æ´¢¹ý³Ì,ÁÙʱ´æ´¢´óµÄÀàÐÍ,Öмä½á¹û¼¯,±í±äÁ¿ºÍÓαêµÈ¡£ÁíÍ⣬Ëü»¹ÓÃÀ´Âú×ãËùÓÐÆäËûÁÙʱ´æ´¢ÒªÇó.
¡¡¡¡2.tempdbÄÚÔÚÔËÐÐÔÀí
¡¡¡¡ÓëÆäËûSQL ServerÊý¾Ý¿â²»Í¬µÄÊÇ,tempdbÔÚSQL ServerÍ£µô,ÖØÆôʱ»á×Ô¶¯µÄdrop,re-create. ¸ù¾ÝmodelÊý¾Ý¿â»áĬÈϽ¨Á¢Ò»¸öеÄ8MB(mdf file:8MB;ldf file:1MB, autogtouthÉèÖÃΪ10%)´óСrecovery modelΪsimpleµÄtempdbÊý¾Ý¿â.
¡¡¡¡tempdbÊý¾Ý¿â½¨Á¢Ö®ºó,DBA¿ÉÒÔÔÚÆäËûµÄÊý¾Ý¿âÖн¨Á¢Êý¾Ý¶ÔÏó,ÁÙʱ±í,ÁÙʱ´æ´¢¹ý³Ì,±í±äÁ¿µÈ»á¼Óµ½tempdbÖÐ.ÔÚtempdb»î¶¯ºÜƵ·±Ê±,Äܹ»×Ô¶¯µÄÔö³¤,ÒòΪÊÇsimpleµÄrecovery model,»á×îС»¯ÈÕÖ¾¼Ç¼,ÈÕÖ¾Ò²»á²»¶ÏµÄ½Ø¶Ï.
¡¡¡¡3.ÈçºÎºÏÀíµÄÓÅ»¯tempdbÒÔÌá¸ßSQL ServerµÄÐÔÄÜ
¡¡¡¡Èç¹ûSQL Server¶Ôtempdb·ÃÎʲ»Æµ·±,tempdb¶ÔÊý¾Ý¿â²»»á²úÉúÓ°Ïì;Ïà·´Èç¹û·ÃÎʺÜƵ·±,loading¾Í»á¼ÓÖØ,tempdbµÄÐÔÄܾͻá¶ÔÕû¸öDB²úÉúÖØÒªµÄÓ°Ïì.ÓÅ»¯tempdbµÄÐÔÄܱäµÄºÜÖØÒªµÄ,ÓÈÆä¶ÔÓÚ´óÐÍÊý¾Ý¿â.
¡¡¡¡×¢:ÔÚÓÅ»¯tempdb֮ǰ,ÇëÏÈ¿¼ÂÇtempdb¶ÔSQL ServerÐÔÄܲúÉú¶à´óµÄÓ°Ïì,ÆÀ¹ÀÓöµ½µÄÎÊÌâÒÔ¼°¿ÉÐÐÐÔ.
¡¡¡¡3.1×îС»¯µÄʹÓÃtempdb
¡¡¡¡SQL ServerÖкܶàµÄ»î¶¯¶¼»î·¢ÉúÔÚtempdbÖÐ,ËùÒÔÔÚijÖÖÇé¿ö¿ÉÒÔ¼õÉÙ¶à¶ÔtempdbµÄ¹ý¶ÈʹÓÃ,ÒÔÌá¸ßSQL ServerµÄÕûÌåÐÔÄÜ.
¡¡¡¡ÈçÏÂÓм¸´¦Óõ½tempdbµÄµØ·½:
¡¡¡¡(1)Óû§½¨Á¢µÄÁÙʱ±í.Èç¹ûÄܹ»±ÜÃâ²»ÓÃ,¾Í¾¡Á¿±ÜÃâ. Èç¹ûʹÓÃÁÙʱ±í´¢´æ´óÁ¿µÄÊý¾ÝÇÒƵ·±·ÃÎÊ,¿¼ÂÇÌ
Ïà¹ØÎĵµ£º
¼¸¸ö¼òµ¥µÄ»ù±¾µÄsqlÓï¾ä
Ñ¡Ôñ£ºselect * from table1 where ·¶Î§
²åÈ룺insert into table1(field1,field2) values(value1,value2)
ɾ³ý£ºdelete from table1 where ·¶Î§
¸üУºupdate table1 set field1=value1 where ·¶Î§
²éÕÒ£ºselect * from table1 where field1 like ’%value1%’
ÅÅÐò£ ......
SELECT id,ip,from_unixtime(last_task_request_time) t1, from_unixtime(last_task_finish_time) t2
from yq_nodemanage
WHERE node_type=1
ORDER BY t1 DESC;
SELECT sum(unix_timestamp(gather_time)-unix_timestamp(publish_time))/(count(*)*60) from yq_bbs_docinfo
WHERE unix_timestamp(publish_time)>un ......
SQL Server·ÖÒ³3ÖÖ·½°¸±ÈÆ´
´ËתÔØÔ´×ÔÀîºé¸ùµÄblog.×÷ÕßÊÇ΢ÈíµÄMVP!Ï£Íû´ó¼Ò²Î¿¼ÒÔÏÂ3ÖÖ·½°¸,°´Êµ¼ÊÇé¿öÑ¡Ôñ!
½¨Á¢±í£º
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) ......
ÉÏÒ»½Ú½²ÊöµÄÊÇɾ³ý²Ù×÷£¬±¾½Ú½«½²ÊöÈçºÎÖ±½ÓÖ´ÐÐsqlÓï¾ä¡£ Ö±½ÓÖ´ÐÐsqlÓï¾äÊÇʹÓÃfromSql·½·¨¡£ DbSession.Default.fromSql("select * from products").ToDataTable();
ÕâÑù¿´ÆðÀ´Ç×ÇжàÁË°É£¬Ö±½Ósql¾Í¿ÉÒÔÖ´ÐС£
µ±È»Ò²¿ÉÌí¼Ó²ÎÊýµÄ°¡¡£
DbSession.Default.fromSql("select * ......
--
SQL Server£º
Select
TOP
N
*
from
TABLE
Order
By
NewID
()
--
Access£º
Select
TOP
N
*
from
TABLE
Order
By
Rnd(ID)
Rnd(ID) ÆäÖеÄID ......