April 22, 2011 at 1:02 pm
SQL 2008 R2 Ent.
32 cores, 500 GB RAM.
New physical server - 2 weeks old.
I have one user database on the SQL server (Database name = DB1 (almost 1 TB in size)...)
I created a database snapshot (Snapshot_DB1).... I'm now running a reindex on a table with 200 million rows.
use DB1
go
ALTER INDEX [PK_members] ON members REBUILD WITH
(PAD_INDEX = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 85)
Well - the CPU spikes to 99% and the memory is only usine 29.3 GB. The performance on the server is severely degraded...
Anyone experiencing similiar issues with database snapshots?
April 23, 2011 at 12:34 am
1-Total physical or logical Processor ?
2-How much RAM allocated for the SQL Server in SQL Server ?
3-Operating System version and bit
4-SQL Server bit
5-Set MAX_WORKER_THREADS ?
6-Set Cost of Threahold ?
7-Set MAXDOP ?
SELECT ST.TEXT,SP.* FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.CPU > 100
RESULT OF THIS QUERY ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 25, 2011 at 8:05 am
Long story short - I run FusionIO cards in production (4 X 1.2TB DUO Cards for one logical drive and 2 X 640 DUO cards for 2 logical drives).
Well, come to find out the FusionIO cards need to placed specifically on the PCIE bus in a specific order. Our production servers (with the physical different drive layout) work without issues.
I builtout a new physical server (HP980) with FusionIO cards and performance was horrible.
So - after multiple server rebuilds - we mimicked the exact FusionIO physical card location from our primary principal node. Now the server behaves as expected.
Now, all the 1.2 TB Duo cards are sitting on Gen8 slots and the smaller 640 cards reside on the Gen4 slots.
So - if running FusionIO cards, make sure the cards in the correct Gen slot.
April 26, 2011 at 5:26 am
You have already a gaint Server HP 980,send me these information as well
1-Total RAM in a Server ?
2-Total Physical Drives not partitions (According to my understanding FusionIO is work as a drive but its transfer data from FusionIO drive to physical drive at a specific time ?
3-If Physical drives are there then are these drive on SAN etc ?
4-Physical Structure of the Database like mdf and ldf files ?
5-Max Worker Thread = 0 its ok,according to microsoft SQL Server get this value automatically but we can do manually also but leave it
6-Cost of Threshold = 5 its default value
7-MAX DOP value = 4 its ok because you have physical 4 processors
yes they have mentioned it will work on the both bus x4 and x8 but note it x8 is better than x4 for bandwidth,and x16 is better than x8,Its all about bandwidth and data transfer rate per second,I did nt use but It must be a software to manage or control these IO cards,there should be some kind of settings of Read and Write operation as well
Regards,
Syed Jahanzaib Bin hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply