In SQL Server, is there a way to list all the data pages, index pages, IAM pages, etc. for a given user table? Like this:
Data pages: (1, 200), (1, 201)
Index pages: (1, 202)
Or if that is impossible, then is it possible to list only the data pages for a table?
I am reading the official documentation on pages and extents. I've also read an article on the official SQL Server Blog (sadly, I can't link as a new member). These articles do a good job explaining what an extent is.Why use extents at all?I can't find a good example anywhere of how grouping pages into extents make SQL Server work more efficiently. Any good example of what SQL Server would be missing if it didn't have extents at all, and how extents fix this?
View 6 Replies View Relatedfind out the list of databases that have ''ldf'' size greater than ''mdf' using stored procedure?
View 7 Replies View RelatedHow do I get the difference between DatabaseProperty() function and DatabasePropertyEx() Functions and how to get list of parameter that uses DatabaseProeprty() and DatabasePropertyEx() Functions.As in sql 2012 both gives red wavy line when I try to use in query analyser.
View 3 Replies View RelatedIs there any windows command or any tool which would tell me what all 3rd party s/w's or Filtered drivers on my server (like antivirus, monitoring tools like Diskeeper or any I/O throttling s/w etc..)
View 3 Replies View RelatedAny sql script or powershell script which outputs late running jobs? Currently I am using the below script to find out currently running jobs along with duration. But my requirement is to add few more columns to the query which indicates whether jobs is running fine or running behind expected time.
-currently using query to pull running jobs
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS currently_executing_step_id,
[code]...
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
Hello all,
We are using sql server 2005 for full text searching.
I removed some of the words in the noise word file (noiseENU.txt) and rebuilt the catalog. However i find that the changes made to the noise file do not reflect immediately. I had to restart sql server engine before my queries returned results according to the updated noise list. Is there a workaround for this ( wherein there isnt the neccesity of restarting sql server engine....this is becoming a problem on live environments as i cannot restart the server when needed)?
Thanks in advance,
Harish
Hello, everyone:
How to calculate or estimate the pages or a table? I always heard someone said their databases have many pages increase one day. What does it mean?
Thanks
ZYT
I'm not sure what is going on but I created a table in the body of a report. I have set the RepeatOnNewPage property to be true. When I preview it the header is on all pages. Perfect! However when I print preview it or export to PDF the header is on Page 1 thru 6 it skips 7 and 8 and then prints on 9. This is happening on a few of my reports.
Any suggestions?
CardGunner
IF (SELECT OBJECT_ID('t1')) IS NOT NULLDROP TABLE t1GOCREATE TABLE t1 (c1 INT, c2 INT)DECLARE @n INTSET @n = 1WHILE @n <= 454BEGININSERT INTO t1 VALUES (@n, @n)SET @n = @n + 1ENDSELECT name, indid, CASE indidWHEN 0 THEN 'Table'WHEN 1 THEN 'Clustered Index'ELSE 'Nonclustered Index'END AS Type,dpages, rowcntFROM sysindexesWHERE id = OBJECT_ID('T1')name indid Type dpages rowcnt---- ----- ---- ------ ------NULL 0 Table 2 454I have a table containing 454 rows of two columnsof type INT with each being 4 bytesc1 int = 4 bytes+c2 int = 4 bytes=8 bytes per rowIf I entered 454 rows : 454 * 8 = 3,632 byteseach SQL Page is 8KB = 8 * 1024 bytes= 8,192 bytesa data page header takes the first 96 bytesleaving 8096 bytes for data and row offsets.Each record uses a row offset at the end of the pageconsisting of 2 bytes. 454 * 2 = 908 bytes.8096 - 3632 - 908 = 3,556 bytes. Should this befree data bytes?For a heap table, does SQL add an internal uniqueidentifiercolumn also? or my question is when does SQL adda uniqueidentifier? I am reading Inside SQL 2000 andtrying to understand a few things.A uniqueidentifier of 4 bytes gets added when a clustered indexexists but it is NOT a UNIQUE clustered index. AND onlyif duplicate record is added those two records only geta uniqueidentifier value.But in my example it's a heap table with no indexes. Evenon a heap table with no indexes a ROWID or Uniqueidentifierget added? Based on the INSERT statement above allvalues are unique.So what am I missing to understand why 453 rowsmake one data page to be used whereas 454 rowsmake two data pages to be used?Thank you
View 1 Replies View RelatedIf a single row of data spans a page then the column header is not repeated until the next row on the next page.
Is there a way to overcome this?
I am helping a friend with a gamming website. There are pages which displays data from other clans.
Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.
I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.
I am using ASP with MS SQL.
I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.
Any help will be appreciated.
I am staring to lose my mind on all this "work-around" BS in trying to create a dynamic header.
I have a report with contains a table, whose first two lines are a header line in which I want to repeat on each page. Then I have about 20 detail rows of information related to the header. The 21st detail row is a merged cell that contains a subreport relating to the header item.
When printing to PDF, the detail rows consume about half the page and the subreport is anywere from half a page to several pages long. When the subreport is more than half a page, it skips the end of the first page and starts on the top of the next page, leaving the bottom half of the first page blank. No matter what I set the page breaking to, it continues this behavior. In addition, when the subreport data spans more than one page, my parent table's header row will not print to the next page even it the RepeatOnNewPage is set to true.
I am coding this way because I have found no good way of creating a dynamic header that will correctly and consistantly display data that has detail that spans more than one page.
Any thoughts on what I might be doing incorrect?
Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
Is there any way to know the auditing table name in sql server 2008.
I am performing auditing for practice . i selected application log to store the audit logs.
I want to know in which table auditing results wil be stored .
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
Some how reason I don't able to pull the data from table and it's giving me following error..I was using just simple select statement
Msg 605, Level 21, State 3, Line 1'
Attempt to fetch logical page (3:10809) in database 9 failed. It belongs to allocation unit 72057616050421760 not to 72057613925351424.
I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition index and next time I want to rebuild index only on y2015 partition .
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.
i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?
View 4 Replies View RelatedI'm copying date from a sql server 2000 table to a sql server 2008 R2 table using a Linked Server.I need to be sure the remote table is correct before processing it.I first delete all the remote table contents and after that, I insert into remote_table from local_tableWhat happens if the communication is interrupted during the insert?I mean, Can I be sure all records were copied just checking if record count > 0 ?
View 3 Replies View RelatedI need to partitioning the table on which most expensive query run.
Every day 500000 lac records inserted/update in that table
How to create what impact on performance while retrieving the records.
I have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.
ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 6 Replies View RelatedI want to insert lots of data into two tables. For this I want to use table valued parameters and a stored procedure. So, what is the better way for best performance? Using two stored procedures or a single procedure with two parameters? How does SQL Server consume the data if I use a single procedure with two parameters. Is it really streaming the data? I mean, is SQL Server already starting to insert the first rows as soon as it gets it even if the client is still sending more and more rows and than the same with the next table?
e.g. I have a procedure like this:
CREATE PROCEDURE [dbo].[usp_UpdateElements] (
@tvpElementsToInsert As [dbo].[tvpElements] Readonly,
@tvpElementValuesToInsert As [dbo].[tvpElementValues] Readonly) AS
Begin
Insert Into Elements Select * From @tvpElementsToInsert;
Insert Into ElementValues Select * From @tvpElementValuesToInsert;
End
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
I have a question relating to the ANSI_PADDING setting on some existing tables in a SQL Server 2008 R2 database I am working with. When I generated the tables originally I basically programmatically created them by building CREATE scripts within my code. Since I did not explicitly set ANSI_PADDING to ON all these tables they seem to have been created with ANSI_PADDING as OFF. Some of these tables, which I now need to add columns to, contain varchar(n) and varbinary(n) columns.
When I try to alter the tables through Management Studio, SQL Server gives me a warning: "One or more tables have ANSI_PADDING 'off' and will be recreated with ANSI_PADDING 'on'" - this seems to be generated by the ALTER statement which by default sets ANSI_PADDING to ON. Another iteration of the same warning - "Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'".
From what I read regarding ANSI_PADDING it seems ON is definitely the way to go. I just need to know if changing the value may result in any of the existing data in the table to be changed or may have any other unintended side effect, as this may cause problems for me.
I have a simple powershell script, that is writing single row to remote SQL server as sort of keepalive solution for branch machine. It opens System.Data.SqlClient.SqlConnection, SqlBulkCopy writes table, and closes connection.
Sometimes script throws an error in form of - Cannot access destination table.
We are not managing internet connectivity in that place, so I don't know for sure, but can this error be caused by connection timeout that comes when script tries to write ?
I have deleted some data from some table. I dont have any backups and the database in simple recovery Mode. How to recover that data ...
View 15 Replies View Relatedi need to give list all table in my database plaese give me qurity?
thanks a lot
Hello,
If you need to have a liste of tables starting with 'WI', under Oracle for example, with a simple select of a system view (with a filter : table_name like 'WI%') you have the result.
Under SQL Server, how to do this simply (sp_tables ?) ?
Thanks in advance