|
| |
| |
|
|
|
Da Notes -
70-019 - P
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 620, Questions: 50, Time: 150 Min.
|
Da Notes Index
|
|
|
Partition Wizard (enterprise edition only)
|
Take a slice of cubed data to use storage elsewhere (even using a different server). Smaller cubes run faster and take less storage.
|
Performance Tuning
|
· Data file placement:
· Tables joined together on separate physical disks. Tables and non-clustered indexes on separate disks.
· Spread large table across several disks for parallel query (RAID does automatically)
· RAID disks appear to SQL as one disk and cannot be used to place tables or indexes on separate physical disks.
·
· When a client application connects to SQL Server and performance is not good, look at SQL Server and check how to optimize the query/schema for better performance - especially the star/snowflake schema, including schema design, indexing, key relationships, referential integrity, and insufficient data scrubbing
· One possible solution to optimize schema: before processing an OLAP Services cube, declare a primary key in each dimension table. Then:
· For star dimensions, declare foreign key (FK) relationships between each dimension table and the corresponding fact table.
· For snowflake dimensions, you also declare FK relationships between each secondary dimension table and the primary dimension table that it augments.
· Define indexes on each of the primary keys in the dimension tables and in the fact tables, and also on each of the foreign keys in the fact
table(s)
· Perform clean up: remove all Nulls for data items that are being moved into OLAP Services
· SQL Server is self-tuning, and it should auto configure as needed.
· The more RAM the better since RAM cache is much faster than access from disk
· Create and maintain good indexes. Clustered index is in physical order. Clustered indexes are much better than non-clustered indexes for queries that match columns or search for ranges of columns that are mostly non-unique.
· Monitor disk I/O performance - run without disk queuing
· Application and Query Tuning - use SQL Server Profiler to create a workload log, then submit it to the SQL Server Index Tuning Wizard for possible index changes
· Performance Monitor - detects bottlenecks with a set of Performance Monitor objects and counters
· Query Analyzer and Graphical ShowPlan - visually analyze SQL queries and I/O stats
· Max Async I/O - with RAID, max async I/O should be set higher. The goal is to make Checkpoint fast enough to finish before another checkpoint is needed. Command to use is in SQL Server Query Analyzer:
"sp_configure 'max async io', <value>"
· Worker Threads - total number of threads is set with the command: sp_configure option max worker threads. If not enough worker thread is available, thread sharing will occur and will negatively affect performance
· LazyWriter - Produce free buffers during periods of low disk I/O so disk resources are ready. If it cannot keep the free buffer above zero, you should then add more physical disk drives. See max async I/O
· Checkpoint - writes dirty pages to the SQL Server data files. You can adjust Checkpoint's dirty page flushing behavior with max async I/O. For example, if disk queuing occurs at unacceptable levels, decrease max async I/O and add more disks to the disk subsystem
· DBCC SHOWCONTIG - used to show excessive page splitting - Scan Density should be close to 100 percent or rebuild the clustered index on that table using the DROP_EXISTING option to defragment the table.
· FILLFACTOR on the CREATE INDEX and DBCC REINDEX commands allow you to specify what percentage of open space to leave on index and data pages. Please note that PAD_INDEX option is for FILLFACTOR on the non-leaf-level index pages. Optimal value for FILLFACTOR depends upon how much new data will be inserted within a given time frame into an 8-KB index and data page. OLAP systems read more than write so FILLFACTOR should be set at 100 percent. All index and data pages will be filled for maximum I/O performance
|
PIVOT TABLE
|
· Excel usually used as the front end interface (no user interface is provided)
· Middle tier in-process service running as a client of OLAP services.
· OLE DB provider that supports the optional OLE DB for OLAP extensions introduced in OLE DB 2.0 and ADO 2.0
· Provides online and offline data analysis and online access to OLAP data
· Will work with a single local cube partition
· functions as a tabular data provider by supporting a subset of SQL
· functions as a multidimensional data provider by supporting MDX
· enables client applications to create local cube directly from a relational data source.
· functions as a mobile desktop OLAP client that enables users to download from data sources and store the data in a multidimensional structure on a local computer for offline analysis
· To configure a new local data cube with Microsoft Excel
· PivotTable menu, Client Server Settings, click the local data file option.
· To populate a PivotTable Service-style chart with cell values
· Use the Item method of the Cellset object to identify an individual cell.
· Retrieve the cell values by using the FormattedValue property.
|
|
|
|
|
