How Indexes are allocates on pages? And If a CREATE INDEX Statement Executed on a query Window, Query processor meets and executes these query. However it was meet, who decides to separate indexes onto pages? Storage Engine or Query Processor(Query Optimizer)? Does it work like UPDATE-Statements in Query Optimizer?
View 4 RepliesSay I have a report with 3 pages, each page has a different dataset and takes some processing time.
is it possible to give the user the option to just run the first page say if thats all they need?
Reduce the processing time of the report to only that that is necessary?
I in a table or partition, the first page is an IAM-Page if I'm not wrong, this page keeps track of the extents.
In the first extent to where the "first_iam_page" points to, the extent is a mixed extent, therefore the pages can be from different tables or partitions, correct? How does my IAM-Page map the right pages to the corresponding table? the following extents are all uniform of one table type, so I guess it doesnt matter then. But n, how does it keep track of which pages belong to which tables in the first extent?
My second question is, the first IAM-Page is obviously an IAM-Page, but there are also GAM , SGAM and PFS Page files... where are they stored? Because when I create a table and insert a big value(8000) into it, it reserves 16KB for that table, one for the IAM-page, and one for the first data-page. But where can I find the GAM,SGAM and PFS page files? or are they not page files, just some other structures?
We found an interesting 'issue' in one of our productive user database:
The output of msdb..suspect_pages table:
db_idfile_idpage_idevent_typeerror_countlast_update
16 2 22 32015.06.12 10:40
16 2 3 3 3 2015.06.12 10:40
16 2 6 3 3 2015.06.12 10:40
16 2 7 3 3 2015.06.12 10:40
16 2 1 3 3 2015.06.12 10:40
The problem is, that this user database only has one data file and file_id 2 is the log file.I understand that the event_type shows checksum and torn page error but in the log file? DBCC CHECKDB shows no errors and the database is part of an AOAG in synchronized state.We have log backups in every 15 minutes.
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...
1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.
2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.
Any disadvantage of having too many indexes?
View 3 Replies View RelatedWhat are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
[code].....
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies View RelatedI have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
I have a new cluster (2 sync, 2 async) with about 50 databases going from 1 to 200gb ( all of the objects are compressed).That at sql server 2012, sp1 CU7.I have several drives for logs with 200gb of space in there...I am having issues at rebuilding indexes on this env, ie, I have a table with the clustered index heavily fragmented (~80%), and the table has about 60gb of data, uncompressed that should be about 160gb.
The index rebuild is creating a log file big enough as to consume all the space that I have for logs, and that is only 1 table, so for sure my old process to maintain indexes (ola.hallengren code) won't work on this scenario.
I'm trying to improve the loading of some tables with large amounts of data that forms part of an ETL. I was going to try removing any indexes before the inserting to speed up the process, but I had some questions on whether or not I should include the clustered index (assuming one exists).
I was originally planning on including a step to disable all indexes on the destination table using the following:
ALTER INDEX ALL ON MyTable DISABLE
Once the load had finished I'd simply rebuild all the indexes.
should I simply disable the non-clustered indexes?
It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.
Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?
Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...
My index reorganise maintenance plan fails partly due to the disabled indexes
Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...
" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?
IS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;
View 2 Replies View RelatedIs there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies View RelatedScript they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
How to accomplish this task efficiently.
Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
View 2 Replies View RelatedWe are using SQL 2k.Can we shrink the space allocated in SQL databases.Please help.
View 1 Replies View RelatedHi all,
Sql server 7.0
our client is getting the below error while accessing the web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIA
We are running on SQL 2000 on Windows 2003. We have active-passiveclustering set up. We have 16 GB of RAM on each box. This pastweekend, we failed over. I noticed that the Total Server Memory wentfrom about 15.5 GB to 8 GB. I have been trying to figure out why thishas occurred. Any help would be greatly appreciated.
View 1 Replies View RelatedI recently changed the max. memory option in SQL from 24 GB to 30GB but the perfmon counters still only show 24 GB. Any ideas on why it is not recognizing the change? The server has Win 2003 EE and 32 GB of RAM.
View 5 Replies View Related Hi i get an error whenever i try to insert some data into my database, the error basically says that the record has been inserted elsewhere, this might be from the way my code has been formed, can you see any mistakes in my code below; 1 --This stored procedure allocates serial numbers to a rep.
2 --Allocating by taking the Rep_ID as the search criteria input values as input parameters
3 --and start and end range of serial numbers.
4
5 CREATE PROCEDURE wc_Stock_Allocate_Update @Start_RangeID int, @End_RangeID int, @Rep_ID int, @IsAllocate bit, @ROWCOUNT int out , @TotalRequestedCards int out ,@Product_ID int , @OutputMsg varchar(200) out
6
7 AS
8 --BEGIN TRANSACTION
9
10 DECLARE @Start_Range_ID int
11 DECLARE @End_Range_ID int
12
13 SELECT @Start_Range_ID = Serial_ID FROM tblSerial WHERE Serial_No=@Start_RangeID AND Product_ID=@Product_ID
14 if IsNull(@Start_Range_ID,0)<1
15 begin
16 set @OutputMsg = 'Invalid Start Serial No'
17 return
18 end
19 SELECT @End_Range_ID = Serial_ID FROM tblSerial WHERE Serial_No=@End_RangeID AND Product_ID=@Product_ID
20 if IsNull(@Start_Range_ID,0)<1
21 begin
22 set @OutputMsg = 'Invalid End Serial No'
23 return
24 end
25
26 DECLARE @strSQL varchar(5000)
27 DECLARE @ThereIsCriteria bit
28 --DECLARE @TotalRequestedCards int
29
30 SET @ThereIsCriteria =0
31 --SET @TotalRequestedCards = (@End_RangeID - @Start_RangeID)+1
32 DECLARE @RangeDifference INT
33 DECLARE @TotalAlreadyAllocated int
34 SELECT @RangeDifference = COUNT(*) FROM tblSerial WHERE Serial_ID >=@Start_Range_ID AND Serial_ID <= @End_Range_ID
35 SELECT @TotalAlreadyAllocated = COUNT(*) FROM tblSerial WHERE Serial_No >=@Start_RangeID AND Serial_No <= @End_RangeID AND (isnull(Rep_ID,0)>0)
36 --SET @TotalRequestedCards = @RangeDifference - @TotalAlreadyAllocated
37 --if @IsAllocate=0
38 SET @TotalRequestedCards =@RangeDifference
39 IF @IsAllocate = 1
40 BEGIN
41 SET @strSQL = 'UPDATE tblSerial SET Rep_ID=' + LTRIM(RTRIM(CONVERT(CHAR(10),@Rep_ID))) +
42 ', Allocation_Date=GetDate() WHERE Serial_No BETWEEN ' + LTRIM(RTRIM(CONVERT(CHAR(10), @Start_RangeID ))) + ' AND ' + LTRIM(RTRIM(CONVERT(CHAR(10), @End_RangeID ))) + ' AND (tblSerial.Rep_ID=0 OR tblSerial.Rep_ID IS NULL) AND Product_ID=' + ltrim(rtrim(convert(varchar,@Product_ID)))
43
44 END
45 ELSE
46 BEGIN
47 SET @strSQL = 'UPDATE tblSerial SET Rep_ID=0
48 , Allocation_Date= ' + CHAR(39) + '1/1/1999' + CHAR(39) + ' WHERE Serial_No BETWEEN ' + LTRIM(RTRIM(CONVERT(CHAR(10), @Start_RangeID ))) + ' AND ' + LTRIM(RTRIM(CONVERT(CHAR(10), @End_RangeID ))) + ' AND (tblSerial.Rep_ID='+ ltrim(RTRIM(CONVERT(CHAR(10),@Rep_ID))) + ') AND Product_ID=' + ltrim(rtrim(convert(varchar,@Product_ID)))
49
50
51 END
52
53
54 exec (@strSQL)
55 SET @ROWCOUNT = @@ROWCOUNT
56
57 --Make sure this has saved, if not return 10 as this is unexpected error
58
59 IF @ROWCOUNT<1
60 BEGIN
61 RETURN @@error
62 END
63 ELSE
64
65 BEGIN
66
67 print @Start_RangeID
68 DECLARE @Tstamp timestamp
69 DECLARE @Stock_ID int
70
71 IF @IsAllocate = 1
72 BEGIN
73
74 declare @Stock_IDpass int
75 declare @StockSTatus_IDpass int
76 declare @ToRep_IDpass int
77 declare @Start_Range_IDpass int
78 declare @End_Range_IDpass int
79 declare @Product_IDpass int
80
81 set @Stock_IDpass =0
82 set @StockStatus_IDpass =2
83
84 set @ToRep_IDpass = @Rep_ID
85 set @Start_Range_IDpass = @Start_RangeID
86 set @End_Range_IDpass = @End_RangeID
87 set @Product_IDpass = @Product_ID
88
89
90 exec wc_Stock_Update 0,2,@Rep_ID, @Start_RangeID, @End_RangeID, @Product_ID,0
91
92 END
93 ELSE
94 BEGIN
95 DECLARE @Start_Serial_No int ,@End_Serial_No int
96 SELECT @Start_Serial_No =Serial_No FROM tblSerial WHERE Serial_ID=@Start_RangeID
97 SELECT @End_Serial_No =Serial_No FROM tblSerial WHERE Serial_ID=@End_RangeID
98
99 exec wc_Stock_Update 0,2,0, @Start_RangeID, @End_RangeID, @Product_ID,1
100
101 END
102
103 END
104
105 GO
One of our servers has 20GB of memory and SQL server has been allocated 18.5 GB out of it, however SQL Server only uses about 9GB or so.
OS: Windows 2003 Server
MS SQL Server 2000 SP4
awe enabled
min & max server memory (run values) are: 18432
/PAE switch in boot.ini
any help why SQL server is not using 18.5 GB allocated to it, would be greatly appreciated?
Could anybody tell me how to find out the total space allocated to a database excluding the log files in SQL server 2000. Thanks
View 1 Replies View RelatedI'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
We have a database that doesn't seem to be growing on its own correctly (or seems like it isn't). This database has been running really slow for the last week and we think this may be whu
Current size is listed at 963mb while allocated size is 959mb. Trans log size is set to auto-grow and is allocated 4mb
Can we change that number on the fly, and also change the the growth settings without affecting the system?
Being a live system we obviously do not want any downtime, but believe this will help with our slowdown.
Any other ideas would also be appreciated
Thanks!
how can i check how much space is allocated to the database in SQL Server
View 2 Replies View RelatedHi, all,
I found that the SQL2000 EM does not show database space allcoated information, as well as tables and indexes size while SQL 7.0 does.
Someitmes these information are fairly handy.
is there any other easy ways to find out the same info from SQL2000
through Em, or elsewhere ??
Thanks
Anthony
My group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
View 2 Replies View RelatedWhy the Indexes on table slow down the DML operation on table, what is the exact reason?
View 5 Replies View RelatedI am searching for query to find total memory allocated to sqlserver, and how much being used utilized as well cpu utilization percentage .
View 10 Replies View Related