PowerView is Off Last Login: September 5, 2023 3:33 PM AST Ahmad (Available) (0) Contact Us Help

Dashboard Knowledge Service Requests Patches & Updates Community

Dashboard >
Give Feedback...

Copyright (c) 2023, Oracle. All rights reserved. Oracle Confidential.

How to Gather Statistics on a SQL Server Database (Doc ID 904287.1) To Bottom

In this Document Was this document helpful?

Goal Yes
No
Solution
To determine the last time statistics were gathered per index, per table:
Document Details
Gather SQL Server statistics using the recommended method:
Gather SQL Server statistics using the full-scan method:
Type:
References HOWTO
Status:
PUBLISHED
Last Major
Jul 14, 2023
Update:
Jul 24, 2023
APPLIES TO: Last Update:

Primavera Contractor - Version 6.1 and later

Related Products
Primavera P6 Enterprise Project Portfolio Management - Version 7.0 and later
Primavera P6 Professional Project Management - Version 7.0 and later Primavera Contractor
All Platforms Primavera P6 Enterprise
Project Portfolio Management
Primavera P6 Professional
GOAL Project Management

This document provides instructions for gathering statistics against a P6 EPPM or P6 Professional database hosted on a Microsoft Information Centers
SQL Server platform. Information Center: Primavera
Contractor [1381158.2]
SQL Server uses statistical information to estimate the number of rows in the query result to be returned, which enables the
built-in Query Optimizer to create a high-quality query execution plan. If the statistics are out of date (due to a large change or Information Center: Primavera
P6 EPPM And P6 PPM (Cloud
accumulated small changes to the data) the Optimizer is using old data, which causes inefficient query plans to be run and and On Premise) [1353831.2]
results in slower database performance.

Gathering statistics can potentially resolve the following symptoms:

Document References
Loading Data hangs at 98%. Best Practices for Performance
Performance issues in SQL Server. Tuning of the P6 EPPM
Opening projects is slow. Database [1327603.1]
PM.exe is not responding after users enter their login name and password.
The Project Management window disappears after loading data.
Users cannot login to P6, with no error displayed. Recently Viewed

How to Gather Statistics on

SOLUTION a SQL Server Database
[904287.1]
Best Practices for
To determine the last time statistics were gathered per index, per table: Performance Tuning of the
P6 EPPM Database
[1327603.1]
1. Launch the SQL Server Management Studio. SRDC - Process to Provide
2. Select SQL Server Authentication. the
3. Enter the schema owner name (default sa) and password then click Connect. Primavera.CacheService.Log
file to Oracle Customer
For Primavera Contractor, the default password for the sa account is Prima123Vera. Support [2354915.1]
For all current Primavera P6 products, the password is specified as part of the initial instance setup process. Best Practices for
Performance Tuning of the
4. Expand Databases on the left, right-click the P6 or Contractor database name, and select New Query from the pop-up P6 EPPM Database
menu to open a new query window. [1327603.1]
5. Execute the following query: Log File Locations For P6
EPPM Modules (And Enabling
SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, Technologies) [1323009.1]
STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated, Show More
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id), getdate()) DaysOld
FROM sys.indexes A INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),
getdate()) DESC
6. A list of objects displays, along with the last time the statistics were updated for that object and the number of days since
then.

Gather SQL Server statistics using the recommended method:

This utilizes a single command to operate on multiple tables. This method uses sampling, where SQL Server takes a random
sample representing a percentage of database records, to determine which statistics need updating.

This option does not allow full scans - for that, see the next section below.

The stored procedure checks how many rows have been update/deleted/inserted since the last collection. For each table,
if no or few changes have occurred, neither its statistics nor its last stats_date in the above query will be updated. This
will also be reflected in the Results area after running the procedure.

1. Launch the SQL Server Management Studio.

2. Select SQL Server Authentication.
3. Enter the schema owner name (default sa) and password then click Connect.

For Primavera Contractor, the default password for the sa account is Prima123Vera.
For all current Primavera P6 products, the password is specified as part of the initial instance setup process.

4. Expand Databases on the left, right-click the P6 or Contractor database name, and select New Query from the pop-up
menu to open a new query window.
5. Run this SQL command to gather statistics on all database tables:
exec sp_updatestats
6. The process can take up to a minute or two to complete, depending on how much data has changed in the database
tables.

Gather SQL Server statistics using the full-scan method:

Performing a full scan of each table, which examines all of each table's records, results in a more accurate picture of the data for
SQL Server to use to optimize queries and improve performance.

This method updates the statistics on all tables and indexes, regardless of whether its data has changed or not.

This more comprehensive method takes longer to complete than the stored-procedure method above.

Refer to the Microsoft SQL Server Documentation for further information on additional methods for gathering statistics:
SQL Server 2019 - Update Statistics
SQL Server 2017 - Update Statistics

1. Launch the SQL Server Management Studio.

2. Select SQL Server Authentication.
3. Enter the schema owner name (default sa) and password then click Connect.

For Primavera Contractor, the default password for the sa account is Prima123Vera.
For all current Primavera P6 products, the password is specified as part of the initial instance set up process.

4. Expand Databases on the left, right-click the P6 or Contractor database name, and select New Query from the pop-up
menu to open a new query window.
5. Execute the following SQL code to generate a multi-line script to gather statistics using the full scan method on all
database tables:

SELECT 'UPDATE STATISTICS ' + table_name + ' WITH FULLSCAN; ' FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
6. Copy the entire results of the query.
7. Open a new query window, paste the copied script into it, and execute it.
8. Wait for the process to complete.

If desired, a job can be created to gather statistics using SQL Server Maintenance plans - for details please refer to
Microsoft's SQL Server Documentation.

REFERENCES

NOTE:1327603.1 - Best Practices for Performance Tuning of the P6 EPPM Database

Didn't find what you are looking for? Ask in Community...

Related
Products

More Applications & Technologies > Oracle Primavera > Enterprise Project Management > Primavera Contractor > Contractor Application
More Applications & Technologies > Oracle Primavera > Enterprise Project Management > Primavera P6 Enterprise Project Portfolio Management > P6 EPPM Database Install,
Setup and Configuration
More Applications & Technologies > Oracle Primavera > Enterprise Project Management > Primavera P6 Professional Project Management > P6 PPM DB Install, Setup, Config Tools

Keywords
DATA; DYNAMIC ADV PERFORMANCE; GATHER STATISTICS; HANGING; MSDE; PERFORMANCE; SQL SERVER; STATISTICS; UPDATE
Back to Top
Copyright (c) 2023, Oracle. All rights reserved. Legal Notices and Terms of Use Privacy Statement