SQL Server Startup Option -E

Question

Hi,

There is a SQL Server Startup option -E which increases the number of extents that are allocated for each file in a filegroup.

Does anyone has any idea about this flag or usage? Any advantage or disadvantage that anyone noticed?

Thanks.

Answer 1

This will not work on 32 bit machines & its preffered on in OLAP requirements.

---

yup

Answer 2

Yes, 64 bit machine is pre-requisite for it. Regardless of preference... I just need to understand its impact, if anyone has ever used it. Have you used it in OLAP?

Thanks for reply though.

Answer 3

Hi,

There is a SQL Server Startup option -E which increases the number of extents that are allocated for each file in a filegroup.

Does anyone has any idea about this flag or usage? Any advantage or disadvantage that anyone noticed?

Thanks.

Hi Bilal,

-e SQL Server Start option defines

 

-e error_log_path

The fully qualified path for the error log file (typically, C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG). If you do not provide this option, the existing registry parameters are used.

 

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 4

Thanks Sufian,

Infact the option you mentioned is -e while I'm concerned about -E...it was not a typo :). I know about -e.

Answer 5

Thanks Sufian,

Infact the option you mentioned is -e while I'm concerned about -E...it was not a typo :). I know about -e.

Answer 6

I'm not 100% sure, as I've never seen a reference to it before, but on a hunch I assumed it might be helpful for TPC-H or similar workloads, and indeed http://www.tpc.org/results/FDR/tpch/HP_DL380G6_100GB_090409_2P_2.93GHzQC_FDR.pdf shows that this parameter is set during a TPC-H config (page 25 of the report).

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 7

Thanks Richard,

So far your reference is exactly the one which matches my requirement. But I'm not able to understand this thoroughly. Would you be kind enough to expalin this thing to me that what exactly is this TPC-H all about and from where can I find the purpose of given parameters?

Thanks again,

Bilal

Answer 8

couple years late, but I found the answer (as I was curious about it as well)

http://support.microsoft.com/kb/329526

So it sounds like if you have multiple files in a Filegroup, the normal mechanism for allocating extents is fill one extent (8 data pages), then it moves to the next file in the filegroup for the next extent, then on to the next file again...

With this flag enabled, instead of 1 extent per file (round-robin), 4 extents are written to the file before moving on to the next file.

BOL states "Increases the number of extents that are allocated for each file in a filegroup" which just didn't make sense until I read the KB article.

HTH someone

-John

Answer 9

yep - very true

but as I said in 2010 mind its usage in 32 bit env. And along with this you should also check for READahead values which can be controlled with TF 652 & can vary from 256 KB to 8 MB (may be more but not configurable).

Answer 10

Thanks John, infact I had got the answer few weeks later but I couldn't post back on site due to my busy schedule. Though I appreciate your effort and consideration.

Regards.