ARCHITECTURE: How to get total number of pages, used page and data_pages of a table
- Details
- Written by ranjeet
There is a system table named sys.allocation_units which actually store about the page distribution of a table. We can join sys.allocation_units to sys.partitions. Below is the query:
select object_name(object_id)as table_name, au.Total_pages as TotalAllocatedPage,au.used_pages as UsedPage, au.data_pages as DataPages
from sys.allocation_units au
join sys.partitions p on p.partition_id=au.container_id
where object_name(p.object_id)='TestTable'
TABLE: How to get number of rows in a table without querying main table
- Details
- Written by Ranjeet
There is a system table sys.partitions which stores the metadata about the table including number of rows. Below is the query.
select object_name(t.object_id) as table_name, sum(rows) as NoOfRows
from sys.tables t
join sys.partitions p on p.object_id=t.object_id
where object_name(p.object_id)='TestP' and p.index_id in(0,1)
group by object_name(t.object_id)
OR
select OBJECT_NAME(dp.object_id) as tableName, sum(row_count) as NoOfRows
from sys.dm_db_partition_stats dp
join sys.tables t on dp.object_id=t.object_id
group by OBJECT_NAME(dp.object_id)
Order by NoOfRows desc
It’s always recommended to use sys.partitions table to get number of records instead of directly querying main table.
To get more details on sys.partitions table please refer my blog here.
Below are the other way we can get number of rows from table
1. Using DMV sys.dm_db_partition_stats
2. sp_spaceused'testp'
3. Using select count(*) or count(1)
ARCHITECTURE: What is GAM and SGAM
- Details
- Written by ranjeet
Guys I will start with page. We have seen in previous article about pages and its different type. GAM and SGAM is one of those types.
Extent allocation is being managed by GAM and SGAM. Actually GAM and SGAM record about the allocation of extent. There are two way to manage allocation of extent:
GAM: Global Allocation map is a type of page. This page records or keep the metadata about what are the extent have been currently allocated. For each extent it stores one bit data. 1 means free and 0 means allocated.
ARCHITECTURE: is Datafile, Page and Extent
- Details
- Written by ranjeet
Datafile: Datafile is a physical file in which SQL server use to store the actual data. SQL server store data and log in separate files.
Page: Datafile are logically divided/managed by pages which is a fundamental unit of data storage. SQL server perform read write operation on page level. Means if SQL server needs to read any particular record, it will bring whole page to memory. Page is of 8 KB in size.
Extent: Collection of continuous eight pages is called an extent. Pages are managed by extent. So, when we say that if some amount of space has been allocated. It actually allocate in extent. So, we can say space management is done by extent or on extent level.
SECURITY: What is "Error: 18456, Severity: 14, State: 38."
- Details
- Written by Ranjeet
I was worried to see error "Error: 18456, Severity: 14, State: 38." from long time in my production SQL server log. By seeing error you can not predict from where this error belongs but if you see error library number 18456, then able to come to know that its belongs to failed attempt.
Now since I have idea its belongs to failed attempt. we can trace this in SQL profiler and know all the details. Like from which account this attempt is happening. For which database its trying to connect etc.
Start SQL profiler and select below event:
Error Log
User Error Message
Audit Login failed
T-SQL: How to get SQL server re-start time
- Details
- Written by Ranjeet
There are multiple way to check SQL server start time. We can read the SQL server log or you can check event log on OS level. But the best way for DBA to check is using DMV as below query:
select dosi.sqlserver_start_time, dosi.* from sys.dm_os_sys_info dosi
T-SQL: How to run a query on all the databases
- Details
- Written by Ranjeet
Hey, till now if I use to run a query on all the databases only by opening cursor. Today I learnt about a system stored procedure which can run your query on all the databases. So, Now I can run a query on all the databases by two ways
Opening the cursor and
By using system stored procedure sp_msforeachdb
Example:
sp_msforeachdb 'select * from sys.objects'
I will have more examples with you once will test with different way.
T-SQL: How to reset query timeout setting in sql server
- Details
- Written by Ranjeet
Use below sp_configure system procedure to "reset remote query timeout" settings on server level
exec sp_configure'remote query timeout', 60
reconfigurere
exec sp_configure
exec sp_configure'show advanced options',1
reconfigure
exec sp_configure
exec sp_configure'remote query timeout', 60
reconfigure
exec sp_configure