SQL Q&A Memory Configuration, Performance Profiling, Setting Your Fill Factor, and More
Let's see how to attach PerfMon logs to Profiler:
- Start Performance Monitor and begin capturing information from the database server.
- Create a new counter log under Performance Logs and Alerts and enter a name for your new log.
- Add new counters, such as % Processor Time. You'll also want to set your logging to start either manually or on a scheduled basis using the Schedule tab.
- Click OK and, if you elected the manual option, be sure that you start logging.
- Set up a trace on your SQL Server through Profiler. You can do this by clicking New Trace on the File menu. Make sure to include the StartTime and EndTime in your trace, then name the trace and set it to save to a file. Finally, you must blast your server to simulate some transaction activity, then stop capturing both Performance Monitor and Profiler data.
- In Profiler, select Import Performance Data from the File menu. Next, choose the location where you stored your PerfMon log and select File | Open | Trace. Finally, select the location where you stored your Profiler trace.
USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO --You must restart SQL Server Engine for changes to take effect.
--Create an Item table USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO; --Create a unique index on colum Col_A of Item table with a Fill Factor value of 70 CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO
USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.