June 11, 2003 at 12:25 pm
is there a way to find out what tables are pinned?
June 11, 2003 at 1:02 pm
sp_tableoption 'tablename',@OptionName ='pintable'
John Zacharkan
John Zacharkan
June 11, 2003 at 1:08 pm
Does pinning a table make a large differance?
Sounds intersting. Never hear of it before.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 11, 2003 at 2:14 pm
I am looking at a way to find out what are the tables that are currently pinned?
sp_tableoption gives you an option to pin or unpin the table. I would also like to know what happens if we try to pin the table that is already pinned?
June 11, 2003 at 3:02 pm
exec sp_msforeachdb 'use
select ''..['' + name + '']''
from sysobjects
where objectproperty(id, ''TableIsPinned'') = 1'
Cheers,
- Mark
June 11, 2003 at 8:21 pm
cool.thanks
June 12, 2003 at 10:41 am
personally I did not find it useful
here is some script that uses a table withg 13 million rows:
SELECT top 1000000 * from sku
/* 2:17 sec */
DECLARE @db_id int, @tbl_id int
USE dw_prod
SET @db_id = DB_ID('PROD')
SET @tbl_id = OBJECT_ID('PROD..Sku')
DBCC PINTABLE (@db_id, @tbl_id)
IF OBJECTPROPERTY (object_id('lu_sku'),'TableIsPinned') = 1
PRINT 'TableIsPinned'
/*
SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0
*/
SELECT top 1000000 * from sku
/* 2:12 sec */
DECLARE @db_id int, @tbl_id int
USE dw_prod
SET @db_id = DB_ID('PROD')
SET @tbl_id = OBJECT_ID('PROD..SKU')
DBCC unpintable (@db_id, @tbl_id)
IF OBJECTPROPERTY (object_id('lu_sku'),'TableIsPinned') = 1
PRINT 'TableIsPinned'
regards
June 12, 2003 at 12:57 pm
tulcanla,
I played around with it a bit and found a HUGE difference when adding a where to the query.
Normal selects and counts made no differance.
Did you use a condition in your select? If so, why is it so much faster for me?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 12, 2003 at 3:27 pm
I find I usually get more indicative results by preceding each test with DBCC DROPCLEANBUFFERS
Cheers,
- Mark
June 13, 2003 at 1:59 am
If you mark a table as pinned then it has little or no affect the first time you carry out an operation after it was pinned. It is the 2nd and subsequent time that it works.
Pin your table, do a SELECT * FROM your table then do your test.
The downside to pinning a table is that you are effectively reserving a huge block of memory just for that table. This is then no longer available to the rest of SQL Server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply