SQL Server Trace Flags

Question

Post reply

SQL Server Trace Flags

Viewing 15 posts - 16 through 30 (of 69 total)

You must be logged in to reply to this topic. Login to reply

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 1

Trace flag 8030 for SQL 2005

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

FIX: Query performance may decrease after SQL Server 2005 has been running for a while

Symptoms

Consider the following scenario. A server has been running for a while. Then, you run a query in Microsoft SQL Server 2005. In this scenario, the query performance may be slow.

<...>

This problem occurs only on 64-bit servers that have more than 16 gigabytes (GB) of physical memory.

Resolution

You do not have to restart the computer after you apply this hotfix. However, after you apply this hotfix, you must add the -T8030 option as a SQL Server startup parameter to enable trace flag 8030. Then, you must restart the SQL Server service for the hotfix to take effect.

Bennett Scharf SSC-Addicted Points: 465 More actions · Answer 2

Here are two useful reference URLs for trace flag 610:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx

http://msdn.microsoft.com/en-us/library/dd425070%28SQL.100%29.aspx

yup SSCarpal Tunnel Points: 4490 More actions · Answer 3

Hey all mrpolecat, chairleg, Ron Klimaszewski and Bennett Scharf; appreciate all your contributions & I'm certainly looking to include all this in the next update.

FVC Mr or Mrs. 500 Points: 598 More actions · Answer 4

traceflag 9059; was for sql server 2000 SP4 (to obtain again SP3 level behaviour for specific cases)

see http://support.microsoft.com/?id=899976

yup SSCarpal Tunnel Points: 4490 More actions · Answer 5

yup

SSCarpal Tunnel

Points: 4490

June 4, 2010 at 3:33 am

#1176424

Thanks FVC, its on the list now.

isheej SSC Journeyman Points: 90 More actions · Answer 6

Great list. New one (undocumented) for SQL 2005 SP3 and higher: 8038. Use old 3 millisecond timer instead of new 1 millisecond timer. Set this trace whenever conflicts exist with power management software. Symptom is sever clock drift.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks - this is a good resource.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yup SSCarpal Tunnel Points: 4490 More actions · Answer 8

Thanks for your input isheej; i'll include this in next update.

SQLCharger SSCommitted Points: 1958 More actions · Answer 9

Useful compilation, thanks!

Cheers,

JohnA

MCM: SQL2008

sknox SSChampion Points: 12388 More actions · Answer 10

204 A backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the select list.

Umm... surely you can have items in the group by clause that aren't in the select list? You can't have items in the select list that aren't either aggregated or in the group by clause, but the reverse is acceptable even with flag 204 off.

yup SSCarpal Tunnel Points: 4490 More actions · Answer 11

yup

SSCarpal Tunnel

Points: 4490

June 8, 2010 at 8:21 pm

#1177768

Let me check on that.

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 12

Trace flag 4618 for SQL 2005

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

FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

Apply trace flag 4618 to limit the number of entries per user cache store. Using trace flag 4618 can incur a small CPU overhead because this trace flag removes old cache entries as new entries are inserted. The trace flag performs this action to limit the size of the cache store growth. However, the CPU overhead is spread over time.