SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Creating an index
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/29/2013 :  05:34:56  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:


ya. I got you.

do u mean bookmark lookups means hard disk seeks?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.



Not quite. A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.

I just meant it takes longer for SQL Server to retrieve the record from disk as compared to memory.



yes. This is true.
generally we say that data is logically organized in the form of table right, so the rows of a table(say that we dont have partitions of our table) are stored in a heap or b-tree.
In turn a heap is organized into allocation units.
These allocation units are organized into pages and extents.

And finally all the data is organized into operating system files in the lowest layer right?

So my doubt is,
Ultimately, all the data is stored in hard disk. And the data which is currently used is stored in memory.

And obviously data in memory should be in the form of tables i guess.

So now which memory is organized into the heap,allocation units,pages and extents.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/29/2013 :  05:54:28  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:


ya. I got you.

do u mean bookmark lookups means hard disk seeks?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.



Not quite. A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.

I just meant it takes longer for SQL Server to retrieve the record from disk as compared to memory.



Please dont mind.
I may type big posts. But i do that to ask you clearly about my doubt.

If you are facing any inconvenience, please feel free to tell me.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 10/29/2013 :  10:11:23  Show Profile  Reply with Quote
quote:

So my doubt is,
Ultimately, all the data is stored in hard disk. And the data which is currently used is stored in memory.



Sure ask any question and thanks for being polite about it. I can't say I will answer all of them though.

I don't understand what is your doubt?
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/29/2013 :  10:30:11  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:

So my doubt is,
Ultimately, all the data is stored in hard disk. And the data which is currently used is stored in memory.



Sure ask any question and thanks for being polite about it. I can't say I will answer all of them though.

I don't understand what is your doubt?



Hello..

My doubt is that...
In memory, how the data is organized. i.e, in what structure?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 10/29/2013 :  13:04:04  Show Profile  Reply with Quote
quote:


Hello..

My doubt is that...
In memory, how the data is organized. i.e, in what structure?




I don't know too much about how the data is organized in memory. A lot of that would be internal to SQL Server and could change from version to version. In my opion it is just good to be aware that your Data may be in the cache (i.e. memory) or it may be in disk, at any given time. SQL Server will try to leave data in memory in case it needs it again. But in most cases the memory on a given server is smaller than the total of your Databases - this means it can't keep everything in Memory and will eventually have to get rid of something in order to make space to bring something else in.

So Data is constantly flowing between memory and file, this is one reason you can't even copy a .mdf file unless you detach it (let alone delete it).

I find this query gave me insight. Try it, if you get the chance. If a query is busy, you can run this query several times and if you notice the Buffer_Pages of a table in the query is constantly increasing, this means SQL Server is busy bringing records from disk to memory.



-- Cache usage by table

WITH src AS
(
   SELECT
       [Object] = o.name, [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id], p.index_id, au.allocation_unit_id
   FROM
       sys.partitions AS p INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id] INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id 
)
SELECT
   Buffer_Pages = COUNT_BIG(b.page_id),
   src.[Object], src.[Type], src.[Index], src.Index_Type
FROM
   sys.dm_os_buffer_descriptors AS b left outer join src
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID() and Object is not null
GROUP BY
   src.[Object], src.[Type], src.[Index], src.Index_Type
ORDER BY
   buffer_pages DESC
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/29/2013 :  13:22:46  Show Profile  Reply with Quote
i will check that now.

another issue..

can we create a non clustered index on a view in SQL Server 2008?

I know that we can create a clustered index on a view by using with schemabinding clause.

but i want to know whether it is possible to create indexed views with a non clustered index or not.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/29/2013 :  14:08:55  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:


Hello..

My doubt is that...
In memory, how the data is organized. i.e, in what structure?




I don't know too much about how the data is organized in memory. A lot of that would be internal to SQL Server and could change from version to version. In my opion it is just good to be aware that your Data may be in the cache (i.e. memory) or it may be in disk, at any given time. SQL Server will try to leave data in memory in case it needs it again. But in most cases the memory on a given server is smaller than the total of your Databases - this means it can't keep everything in Memory and will eventually have to get rid of something in order to make space to bring something else in.

So Data is constantly flowing between memory and file, this is one reason you can't even copy a .mdf file unless you detach it (let alone delete it).

I find this query gave me insight. Try it, if you get the chance. If a query is busy, you can run this query several times and if you notice the Buffer_Pages of a table in the query is constantly increasing, this means SQL Server is busy bringing records from disk to memory.



-- Cache usage by table

WITH src AS
(
   SELECT
       [Object] = o.name, [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id], p.index_id, au.allocation_unit_id
   FROM
       sys.partitions AS p INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id] INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id 
)
SELECT
   Buffer_Pages = COUNT_BIG(b.page_id),
   src.[Object], src.[Type], src.[Index], src.Index_Type
FROM
   sys.dm_os_buffer_descriptors AS b left outer join src
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID() and Object is not null
GROUP BY
   src.[Object], src.[Type], src.[Index], src.Index_Type
ORDER BY
   buffer_pages DESC




How can we see the buffer pages of a table..?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/31/2013 :  03:31:56  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

i will check that now.

another issue..

can we create a non clustered index on a view in SQL Server 2008?

I know that we can create a clustered index on a view by using with schemabinding clause.

but i want to know whether it is possible to create indexed views with a non clustered index or not.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.



I got this issue confirmed..

First clustered index(WITH SCHEMABINDING clause) should be created it seems.
Then we can create any number of non clustered indexes it seems.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 10/31/2013 :  07:18:10  Show Profile  Reply with Quote
sgondesi, I got an email from MSSQLTips about a free webinar about indexing today https://www2.gotomeeting.com/register/402555122


djj
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/31/2013 :  08:25:48  Show Profile  Reply with Quote
quote:
Originally posted by djj55

sgondesi, I got an email from MSSQLTips about a free webinar about indexing today https://www2.gotomeeting.com/register/402555122


djj



Thanks for that sir.

why dint you reply for the post yesterday?
did you see the post?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 10/31/2013 :  08:58:47  Show Profile  Reply with Quote
I have days that I do not have time to study a post to formulate an answer.

djj
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 10/31/2013 :  09:30:59  Show Profile  Reply with Quote
quote:
Originally posted by djj55

I have days that I do not have time to study a post to formulate an answer.

djj



Its ok thank you, have a good day!

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 11/19/2013 :  03:49:05  Show Profile  Reply with Quote
Somebody please tell me what a bookmark lookup is?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 11/19/2013 :  06:47:31  Show Profile  Reply with Quote
sgondesi, please start a new thread.

djj
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000