You have a data warehouse named DW1. All data files are located on drive E. You expect queries that pivot hundreds of millions of rows for each report. You need to modify the data files to minimize latency. What should you do?
Suggested Answer:B🗳️
The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code. References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175527(v=sql.105)?redirectedfrom=MSDN
When PSSSQL first came up with this advice, it was really for a particular use-case, when lots of objects are being created in tempdb in succession by different processes. But they didn't make it clear. And now it's baked into the installer *sigh*
The contention that *could* occur in tempdb is on the SGAM page. The first extent of a new table is a mixed extent and SQL Server will check the 1st SGAM of the file, and this page potentially becomes a bottle neck. With having multiple files, object creation is done round-robin. So 2 processed go into 2 different tempdb files and can create new objects without contention. If we are pivoting lots of data, we could be spilling to tempDB. Adding files will not solve (work) table creation issues, how many work tables are going to get created in tempdb. Putting files on different spindles would potentially help
-the question is not about tempdb, so it's not B or C
-I would choose rather A
see - https://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/
upvoted 1 times
...
This section is not available anymore. Please use the main Exam Page.70-767 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Lukis92
4 years, 5 months agoHoglet
4 years, 4 months agoBelierOne
4 years, 5 months agoDudeHere
4 years, 5 months agoSlava_bcd81
4 years, 7 months ago