Success and Failures in Pushing Ideas in Startups vs Matured Companies

Over the past 4 months, I've been working with really small teams, and the difference in communication dynamics compared to larger teams has been striking.

In my previous roles in product and consulting, I achieved success but with a considerable amount of effort spent convincing and negotiating with numerous people. Here are some of the specific challenges faced when pushing ideas in larger, more mature companies compared to nimble startups:

Increased Lines of Communication: With more team members in mature companies, ensuring everyone is on the same page becomes significantly harder. There's a higher risk of changes in approach, iterations, feedback, and information being lost or mistranslated as it travels through various levels. In contrast, startups often have flatter structures, making communication more direct and less prone to distortion.

Slower Decision-Making Processes: Larger teams often have more layers of approval, which can slow down decision-making. Every stakeholder has their own priorities and concerns, adding to the complexity. Startups, with their smaller teams, can often make decisions more quickly, which allows for faster iterations and innovation.

Greater Need for Consensus: In smaller teams typical of startups, reaching a consensus or getting buy-in for new ideas is often easier. Larger teams in mature companies require more effort to align everyone's visions and goals. This can lead to lengthy discussions and compromises, which may dilute the original idea.

More Stakeholders to Convince: Larger teams come with more stakeholders, each with their own perspectives and interests. This multiplicity can make it challenging to get everyone on board with a new idea. Startups, on the other hand, usually have fewer stakeholders, and the founders or key decision-makers are more accessible, simplifying the process of getting buy-in.

However, the journey you take, whether in a startup or a mature company, will reward you for the risks and decisions you choose to travel with. Each environment has its own set of challenges and rewards, but understanding these dynamics can help in navigating them more effectively.

Keep Going!!!

Operators - Revisited

I shifted my focus to Linux-java. I always love SQL Server :). Thanks to Roji & Balmukund my SQL mentors. This blog post is on operators. This is based on my earlier presentation on SQLCommunity site link

Operators

• Fundamental Unit of Execution
• Blocking (Hash, Sort – In memory Operators) & Non-Blocking (Seeks, Scans etc..)
• Common Operators – Seek, SCAN, Bookmark Lookup, JOINs (Nested, Merge, Hash)
• Execution Plan need to be interpreted from right to left
• An operator can have More than One Input and One Output

Following different kinds of operators we will check

•  Join Operators
•  Bookmark lookup operator
•  Seek & Scan Operator
•  Spool Operator
•  Option for (Hint)
•  Fast N (Hint)

Example Walkthru
--STEP 1

CREATE TABLE TSCAN
(
    Col1 INT IDENTITY(1,1),
    Col2 VARCHAR(40)
)

--STEP 2
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    INSERT INTO TSCAN(Col2)
    VALUES(CONVERT(VARCHAR(20),@I)+'VALUE')
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 3 (Table SCAN)

SELECT Col1 FROM TSCAN WHERE Col1 = 100

Clustered Index Seek
--STEP 4
--Now Lets Create an Index on Col1
CREATE CLUSTERED INDEX CIX_TSCAN on TSCAN(Col1)

--STEP 5 (Clustered Index Seek)
SELECT Col1 FROM TSCAN WHERE Col1 = 100

--STEP 6 (Clustered Index SCAN)
SELECT Col1, Col2 FROM TSCAN WHERE Col2 = '100Value'

--STEP 7 (Demo Bookmark Lookup)

ALTER TABLE TSCAN
ADD COL3 CHAR(100)
--Populate Data
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    UPDATE TSCAN
    SET COL3 = (CONVERT(VARCHAR(20),@I)+'COL3')
    WHERE Col1 = @I
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 8 (Bookmark Lookup)
CREATE INDEX IX_COL3 ON TSCAN (COL3)
SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

--STEP 9 (Resolving Key Lookup)

--Create Index on Col3, Col2 for above query to result in Index Seek
DROP INDEX TSCAN.IX_COL2_COL3
CREATE INDEX IX_COL2_COL3
ON TSCAN(COL3)
INCLUDE (COl2)

SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

(Working with JOINS)

--NESTED LOOPS (Smaller Inner Sets, Join Columns Indexed)
CREATE TABLE NLOOPTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE NLOOPTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @I INT
DECLARE @J INT
SET @I = 1
SET @J = 1
WHILE 1 = 1
BEGIN
    IF @I < 1000
    INSERT INTO NLOOPTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    IF @J < 100000
    INSERT INTO NLOOPTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @I = @I + 1
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--NESTED LOOP
SELECT
N1.Col1, N2.Col2
FROM NLOOPTable1 N1
JOIN NLOOPTable2 N2
ON N1.Col1 = N2.Col1

--Merge JOIN

--TWO Sorted (Indexed) Tables, Large Tables SQL Server Chooses Merge Join when the Join columns are indexed
CREATE TABLE MergeJOINTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE MergeJOINTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO MergeJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Merge JOIN
SELECT
N1.Col1, N2.Col2
FROM MergeJOINTable1 N1
JOIN MergeJOINTable2 N2
ON N1.Col1 = N2.Col1

 Hash Join
--Two Large Unindexed Tables, No Indexes Defined on Join Columns

CREATE TABLE HashJOINTable1(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE HashJOINTable2(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO HashJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Hash JOIN
SELECT
N1.Col1, N2.Col2
FROM HashJOINTable1 N1
JOIN HashJOINTable2 N2
ON N1.Col1 = N2.Col1

Index Recommendations in Green you can find. Happy Reading!!!!

Using DMVs to find Execution Plan of Currently Running Queries

We would check on currently running queries how do we find execution plan of the queries. For demo purpose would create some tables and try some long running queries

Compiled Plan 

• Compiled plan is product of query optimization
• Stored in object store or sql store
• Compiled plan would specify which table and indexes to access
• Multiple concurrently executing queries can share same compiled plan
• Can be shared between multiple sessions and users

 Executable Plan

• Adding Parameter, variable to compiled plan
• Information specific to one particular execution
• Runtime objects created when compiled plan is executed

Plan Handle

• Cached compiled plan retrieved using plan handle
• sys.dm_exec_cached_plans contains plan handle for every compiled plan
• Plan handle is hash value sql server derives from compiled plan

SQLhandle

• Actual text stored in SQL Manager Cache
• Transact SQL text cached in sql manager cache retrieved using sql_handle

SQL Handle: Plan handle - 1:N

Source: MSDN Blogs. I hope I learnt things right. Sql_Handle and Plan_Handle Explained

Session 1
Step 1
USE TEMPDB
CREATE TABLE DBO.TESTTable
(
Id INT Identity(10,10),
Name VARCHAR(20)
)
 
STEP 2
--Insert some records with below command. I am trying on SQL 2008 R2
INSERT INTO DBO.TESTTable(Name)
Values ('Testvalue')
GO 50

--STEP 3 --Lets try running below statement, This is a infinite loop

While 1 =1
BEGIN
      SELECT Top 1 Name FROM DBO.TESTTable
END
 
Open another query session and lets try to get details on this query from DMVs
Session 2
STEP 1
USE TEMPDB
--View the query and Plan of currently running queries
SELECT *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
 
Output would be like below

Take the plan handle value displayed in the right and run below query with plan handle value
 
STEP 2
SELECT *
FROM sys.dm_exec_sql_text(0x060002003A882A10B820FF04000000000000000000000000)
--Plan Handle

You will see the running query details as below

You will see the running query details as below

STEP3

To view the execution plan of the query run the below query with the plan handle value

SELECT *

FROM sys.dm_exec_query_plan(0x0600020056218802B880F104000000000000000000000000)

This would show us the XML show plan as below

Take the plan and check further. More details on execution plan you can find on execution plan tagged posts.


More Reads
GOTCHA: SQL Server changes query plan without changing plan_handle


Happy Reading!!

SQL Query Execution

SQL Query Execution Phase Involves below Stages

1. Parsing
2. Normalization
3. Optimization
4. Caching
5. Wait for Memory to Execute the Plan
6. Execute
7. Return Results

Summary

• Parsing - Validate Syntax of Query, Split Query into Operators, Expressions, Keywords. Output is Parse Tree.
• Normalization - Validate Objects, Replaces views by definitions, Algebrized tree is output. This is input to Optimizer to generate execution Plan.
• Optimizer - Responsible for generating Query Plan. Cost Based Optimizer, based on Table Statistics, Indexes, JOIN selection. Trivial or Straight Forward Optimization and Full Optimization are two phases involved in identifying Optimal Plan.
• Compiled Plan is Output from Optimizer. The compiled plan for this query, though, would tell SQL Server exactly which physical query operators to use. Compiled plans are reentrant, which is to say that if multiple users are simultaneously executing the same stored procedure, they can all share a single compiled plan.
• Execution contexts - Information Specifix to Particular user, Execution. Cannot be Shared simultaneously. Every Execution Context is linked to a compiled Plan.
• SQL Server breaks queries down into a set of fundamental building blocks that we call operators.
• Operators can be either Physical or Logical. JOIN is a logical operation wheras nested loop join is a Physical Operator.
• Physical Operators implement operation defined by logical operators. Physical Operators answers three method calls INIT() - Initialize itself with required data structure. GETNEXT() - Call first, next subsequent rows, CLOSE() - Clear up operations.
• Few Tricky Physical Operators
• Lazy spool - . The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database.
• Spool - The Spool operator saves an intermediate query result to the tempdb database.
• Caching - Plans are Cached in Cached stores for SPs, Functions, Adhoc Query Plans, Auto Parameterized Plans

You can also check recorded session on Execution Plan Anaysis in SQLCommunity site link
Intepreting Execution Plans

Ref - Link


Why should I create an index?
I Smell a Parameter!
Microsoft SQL Server Execution Plans: From Compilation, to Caching, to Reuse
SQL Query Execution Notes

Happy Learning!!!!

Understanding Query Processing and Query Plans in SQL Server

Compiled list of posts for Learning Execution Plan

• Superb presentation avalilable in this link - http://blogs.msdn.com/craigfr/attachment/8508493.ashx
• Execution Plan cost formulas explained here - http://perftuning.com/pdf/ExecutionPlanCostFormulas.pdf
• SQL Server Execution Plan Analysis Video
• Introduction to How to Interpret SQL Server Execution Plans
• Spool operators in query plan...
• Estimated vs Actual Plans
• Sorted Seeks
• Interpreting SQL Server execution plans
• SQL execution plans, part 2: SQL compilation
• Adventures in TSQL: SQL Server Query Performance Analysis using DMVs
• SQL Server Query Execution

Happy Learning!!!