July 30, 2014 at 8:34 am
I believe that one form of "compression" is being overlooked and it's available in all editions of SQL Server... if the Clustered Index has no temporal-like key to match thee order of inserts of the key columns of the Clustered Index are updated on a regular basis, there will be a huge number of page splits. In such cases, simply rebuilding or reorging the Clustered Index (and the non-clustered indexes, as well. It's amazing how much empty space they can contain on systems that have no regular, proper defragmentation jobs going on.
So my first question before any other consideration would be...
Do you have properly written index defragmentation and stats update jobs running? If not, set one up, run it, and then look again.
As for the 20GB size of the table, that's actually pretty small, nowadays. I wouldn't worry about the actual size of the table unless, as I said, it has a huge amount of free space in the table caused by page or extent splits.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2014 at 10:37 pm
Chowdary's (7/30/2014)
Hi Eirikur Eiriksson,Pls find the below
1. What is the output of this query?
A:Enterprise Edition (64-bit),3
2. Can you post the CREATE TABLE script for the table?
A.PFA
3. Is this a production server :Yes
Regards
Chowdary....
Few of more questions,
4. How many rows are in the table
5. What is the output of this query? SELECT SUM(DATALENGTH([MEMBERPHOTO])) AS PHOTO_SIZE FROM [dbo].[tblSalesMemberMaster]
6. How is the [MEMBERPHOTO] used?
7. Where does the [MemberNo] value come from?
8. Is the [MemberNo] ever increasing?
Reason for asking is that the [MEMBERPHOTO] (Image) will not compress and Image is also a depreciated data type. An option here might me to move the column into another filegroup, table or filestream, certainly cannot recommend keeping it in the primary filegroup.
😎
July 31, 2014 at 12:57 am
Hi All,
When am trying to do shrink by using the DBCC command (DBCC SHRINKFILE(DB NAME,50)) am getting the following error
" could not adjust the space allocation for file ibhmLive"
Can anyone help me out....
Regards
Chowdary...
July 31, 2014 at 4:11 am
Sounds like you're trying to make it smaller than you have data in it. You can't do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply