July 3, 2009 at 9:48 pm
hi all,
I have created cluster index on tables on new filegroup and after that i delete cluster index but after checking from sysobjects and sysindexes.
Tables are now showing that they belong to two file group.
Likewise to check i ran
select o.[name],i.[groupid] from sysobjects o,sysindexes i where i.[id]=o.[id].
OUT PUT WAS LIKETHIS
NAME group id
ABC 1
ABC 2
XYZ 1
XYZ 2
DONOT THEY Suppossed to be in new filegroup '2'.
Where i am going wrong help?
July 4, 2009 at 1:55 am
Nero (7/3/2009)
select o.[name],i.[groupid] from sysobjects o,sysindexes i where i.[id]=o.[id].
Can you post the result of this query?
select o.[name],i.[groupid] from sysobjects o
INNER JOIN sysindexes i ON i.[id]=o.[id]
July 4, 2009 at 2:03 am
Do you have nonclustered indexes on that table? If so, they may be in different filegroups to the base table
Add i.name and i.indid to the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2009 at 6:59 am
hi all,
Result is same as posted above and i am not using non cluster index
where i am going wrong?
Thanks,
Nero
July 4, 2009 at 7:03 am
Can you please post the results after including i.name and i.indid as suggested by Gail?
July 4, 2009 at 11:35 am
Please run this and post the results
select t.name, i.type_desc, i.name, ds.name
from sys.tables t
inner join sys.indexes i on t.object_id = i.object_id
inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2009 at 10:51 pm
hi ,
It is giving invalid object name for above query.
We have sql server 2000...
One question apart from above
If i add one more log file to my database.how this will help me in improvement in performance.
Thanks,
Neerav
July 6, 2009 at 1:40 am
Nero (7/5/2009)
hi ,It is giving invalid object name for above query.
We have sql server 2000...
Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions, like happened here.
select o.name, i.indid, i.name, fg.groupname
from sysobjects o
inner join sysindexes i on o.id = i.id
inner join sysfilegroups fg on i.groupid = fg.groupid
where o.xtype = 'U'
If i add one more log file to my database.how this will help me in improvement in performance.
It won't help at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 10:24 pm
SCTIPUSERS
0SCTIPUSERSPRIMARYSCTIPUSERS
255tSCTIPUSERSPRIMARYINCORPORATIONTYPES
0INCORPORATIONTYPESPRIMARYPERFSTAT_B1_EXT
0PERFSTAT_B1_EXTPRIMARYPERFSTAT_B1_EXT
2_WA_Sys_SUPP_CODE_05EEBAAEPRIMARYPERFSTAT_B1_EXT
3_WA_Sys_FILENO_05EEBAAEPRIMARYPERFSTAT_B1_EXT
4_WA_Sys_BidNo_05EEBAAEPRIMARYPERFSTAT_B1_EXT
5_WA_Sys_SO_SRNO_05EEBAAEPRIMARYITEMCHARACTERISTICS
0ITEMCHARACTERISTICSPRIMARYtblBidFormsFlow
0tblBidFormsFlowPRIMARYtblBidFormsFlow
2_WA_Sys_FormName_0618D7E0PRIMARYtblBidFormsFlow
3_WA_Sys_FormID_0618D7E0PRIMARYtblBidFormsFlow
4_WA_Sys_URL_0618D7E0PRIMARYtblBidFormsFlow
5_WA_Sys_PreFormID_0618D7E0PRIMARYtblBidFormsFlow
6_WA_Sys_PostFormID_0618D7E0PRIMARYtblBidFormsFlow
7_WA_Sys_Active_0618D7E0PRIMARYtblBidFormsFlow
8_WA_Sys_AddedOn_0618D7E0PRIMARYAUTONOMOUSFORMMASTER
0AUTONOMOUSFORMMASTERPRIMARYTenderAwards
0TenderAwardsPRIMARYITEMPARAMETER_NDP
1PK_ITEMPARAMETER_NDPPRIMARYSMS_DETAILS
0SMS_DETAILSPRIMARYtblSupplierLogold
0tblSupplierLogoldPRIMARYITEMCONFIG
0ITEMCONFIGPRIMARYtblEQC
1PK_tblEQCPRIMARYtblEQC
2_WA_Sys_Supp_Code_070CFC19PRIMARYtblEQC
3_WA_Sys_BidNo_070CFC19PRIMARYtblEQC
4_WA_Sys_PRCMANU_070CFC19PRIMARYtblEQC
5_WA_Sys_STOLST_ATTCH_070CFC19PRIMARYtblEQC
6_WA_Sys_MAX_STAFF_070CFC19PRIMARYtblEQC
7_WA_Sys_CERT_TEST_070CFC19PRIMARYtblEQC
8_WA_Sys_PostDate_070CFC19PRIMARYAUTONOMOUSFORMSECTIONS
0AUTONOMOUSFORMSECTIONSPRIMARYTipActions
0TipActionsPRIMARYSTOREDIR_MINOR_NDP
1PK_STOREDIR_MINOR_NDPPRIMARYSMS_MASTER
0SMS_MASTERPRIMARYITEMCONFIGCHARACTERISTICS
0ITEMCONFIGCHARACTERISTICSPRIMARYAUTONOMOUSFORMSECTIONSANDHEADERS
0AUTONOMOUSFORMSECTIONSANDHEADERSPRIMARYTipTriggers
0TipTriggersPRIMARYCITY
1PK_CITYPRIMARYTE_NDP
1PK_TE_NDPPRIMARYSMS_PLACEHOLDER
0SMS_PLACEHOLDERPRIMARYITEMFORMULADESCRIPTION
0ITEMFORMULADESCRIPTIONPRIMARYtblFormSign
0tblFormSignPRIMARYStateWiseTax
0StateWiseTaxPRIMARYStateWiseTax
2_WA_Sys_FileNo_09003183PRIMARYStateWiseTax
3_WA_Sys_Supp_Code_09003183PRIMARYStateWiseTax
4_WA_Sys_BidNo_09003183PRIMARYStateWiseTax
5_WA_Sys_Schedule_09003183PRIMARYStateWiseTax
255tStateWiseTaxPRIMARYAUTONOMOUSFORMULADESCRIPTION
0AUTONOMOUSFORMULADESCRIPTIONPRIMARYranjantest
0ranjantestPRIMARYranjantest
2_WA_Sys_MajorStore_09353BADPRIMARYranjantest
3_WA_Sys_LabelName_09353BADPRIMARYranjantest
4_WA_Sys_OrderBy_09353BADPRIMARYranjantest
5_WA_Sys_Pkey_09353BADPRIMARYranjantest
6_WA_Sys_DirCode_09353BADPRIMARYranjantest
7_WA_Sys_FormName_09353BADPRIMARYranjantest
8_WA_Sys_Section_09353BADPRIMARYranjantest
9_WA_Sys_Mandatory_09353BADPRIMARYranjantest
10_WA_Sys_ContentType_09353BADPRIMARYranjantest
11_WA_Sys_Javascript_09353BADPRIMARYranjantest
12_WA_Sys_width_09353BADPRIMARYranjantest
13_WA_Sys_height_09353BADPRIMARYranjantest
14_WA_Sys_StyleOfContent_09353BADPRIMARYranjantest
15_WA_Sys_multiselect_09353BADPRIMARYranjantest
16_WA_Sys_DataSource_09353BADPRIMARYranjantest
17_WA_Sys_ID_09353BADPRIMARYranjantest
18_WA_Sys_TableName_09353BADPRIMARYranjantest
19_WA_Sys_FieldName_09353BADPRIMARYranjantest
20_WA_Sys_SourceTable_09353BADPRIMARYranjantest
21_WA_Sys_SourceField_09353BADPRIMARYranjantest
22_WA_Sys_DestinationTable_09353BADPRIMARYranjantest
23_WA_Sys_DestinationField_09353BADPRIMARYranjantest
24_WA_Sys_Locked_09353BADPRIMARYranjantest
25_WA_Sys_ColWidth_09353BADPRIMARYranjantest
26_WA_Sys_visible_09353BADPRIMARYTmp_RTemp
0Tmp_RTempPRIMARYTEDATA_NDP
1PK_TEDATA_NDPPRIMARYSOHEADER
0SOHEADERPRIMARYSOHEADER
255tSOHEADERPRIMARYITEMFORMULAVALIDATION
0ITEMFORMULAVALIDATIONPRIMARYAUTONOMOUSVALUETYPES
0AUTONOMOUSVALUETYPESPRIMARYTmp_RTemp1
0Tmp_RTemp1PRIMARYTNDATA_NDP
1PK_TNDATA_NDPPRIMARYPERFSTAT_B3
0PERFSTAT_B3PRIMARYPERFSTAT_B3
2_WA_Sys_SUPP_CODE_0AFD888EPRIMARYPERFSTAT_B3
3_WA_Sys_FILENO_0AFD888EPRIMARYPERFSTAT_B3
4_WA_Sys_BidNo_0AFD888EPRIMARYPERFSTAT_B3
5_WA_Sys_RCNo_0AFD888EPRIMARYBANK_MASTER
0BANK_MASTERPRIMARYTmp_RTemp2
0Tmp_RTemp2PRIMARYtblBannerAds
0tblBannerAdsPRIMARYtblBannerAds
2_WA_Sys_Banner_Display_0B528E49PRIMARYtblBannerAds
255ttblBannerAdsPRIMARYcomgtc1
0comgtc1PRIMARYSTATEMASTER
0STATEMASTERPRIMARYtblUserLog
0tblUserLogFILEGROUP2
Query output was likea bove
July 7, 2009 at 1:53 am
Which of the tables are you concerned is 'duplicate'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply