PINTABLE

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

  • sp_tableoption 'tablename',@OptionName ='pintable'

    John Zacharkan


    John Zacharkan

  • 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!

  • 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?

  • exec sp_msforeachdb 'use

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

    from sysobjects

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


    Cheers,
    - Mark

  • cool.thanks

  • 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

  • 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!

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


    Cheers,
    - Mark

  • 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