RSS

#sqlsat160 notes

11 Oct

Here are some personal notes I took for each session at SQLSaturday Kalamazoo 2012. They may or may not make sense to you but might come in handy for me later.

AlwaysOn

– Application Intent can go in the connection string to scale read operations to a secondary server
– An odd number of votes is needed in the cluster. Doesn’t have to be shared disk.
– Backups need some attention, Copy only and t-logs can be done on replicas
– statistics get stored in TempDB on secondaries
– RCSI probably not possible on primary
– Same service account
– Use PoSH for node voting
– Use Mgmt studio for AD object management not cluster manager
– GUI doesn’t turn on availability group… missing from script
– Make sure to enable Always on with SQL Config manager

Internals

– If SQL is slow its either Capacity, Configuration or Code
– Individual thread waits are show in os_waiting_tasks
– os_wait_stats are aggregated
– |———>|———->|————>
– wait_____signal______execute_________done
– 8KB pages include PageID, NextID, PrevID, OwningID..etc.etc
– traceon(3604) before DBCC Page(dbid, fileid, pageid)
– Bitmap pages are for optimization of allocation (0101101000) <—0's denote freespace
– GAM, SGAM, BCM, DCM, IAM
– Trace Flag 1118 mix extent use CMS and register the same server in a group multiple times to test concurrency
– Writelog waits are most common for log problems such as VLFs
– SleepTask waits could mean xml
– memory grants fall into buckets of queues divided by cost + memory

Trans Replication

– Can adjust retention of commands in the distribution database
– Distribution database can be on separate server from publisher
– can adjust agent retry settings
– sp_browsreplcmds can be helpful
– primary keys are required
– referential integrity just works, can be different RI on subscriber

Optimizer

– STMT -> Parsing -> Binding
– Goetz Graefe worked with Microsoft to help improve optimizer
– Trees of operators in query plans
– 8605 will give you a basic text plan
– query optimizer can use constraints (constant scan instead of table scan example)
– query optimizer is used to help with joining result sets in proper order
– there are sets of rules that can be turned on or off
– DBCC SHOWOFFRULES

Blogging

– sitemap google plugin
– writing order -> Madman -> Architect -> Carpenter -> Judge
– LiveWriter can help with posts and format

Advertisements
 
Leave a comment

Posted by on October 11, 2012 in PASS

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: