Post reply

PINTABLE

  • June 11, 2003 at 12:25 pm

    #64860

    is there a way to find out what tables are pinned?

  • June 11, 2003 at 1:02 pm

    #460869

    sp_tableoption 'tablename',@OptionName ='pintable'

    John Zacharkan


    John Zacharkan

  • June 11, 2003 at 1:08 pm

    #460870

    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

    #460880

    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

    #460887

    exec sp_msforeachdb 'use

    select ''..['' + name + '']''

    from sysobjects

    where objectproperty(id, ''TableIsPinned'') = 1'


    Cheers,
    - Mark

  • June 11, 2003 at 8:21 pm

    #460938

    cool.thanks

  • June 12, 2003 at 10:41 am

    #461103

    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

    #461130

    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

    #461157

    I find I usually get more indicative results by preceding each test with DBCC DROPCLEANBUFFERS


    Cheers,
    - Mark

  • June 13, 2003 at 1:59 am

    #461222

    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.

    LinkedIn Profile

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply